PLSQL Debug

http://st-curriculum.oracle.com/obe/db/11g/r2/prod/appdev/sqldev/plsql_debug/plsql_debug_otn.htm

Print Preview

Developing and Debugging PL/SQL using Oracle SQL Developer

This tutorial contains the following sections:

Viewing Screenshots

Click icon to hide all screenshotsClick icon to hide all screenshots

Note: Alternatively, you can click an individual icon (or image) associated with each step to view (or hide) the screenshot associated with that step.

Oracle Technology Network

Developing and Debugging PL/SQL using Oracle SQL Developer

Purpose

This tutorial shows you how to create, run, and debug a PL/SQL procedure using Oracle SQL Developer.

Time to Complete

Approximately 30 minutes.

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. With Oracle SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. This tutorial focuses on creating, compiling, running and debugging PL/SQL.

Prerequisites

Before starting this tutorial, you should:

  • Install Oracle SQL Developer 2.1 early adopter from OTN here. Follow the readme instructions here.
  • Install the Oracle Database 10g and later.
  • Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:
    alter user hr identified by hr account unlock;
  • Download and unzip the plsql_debug.zip file that contains all the files you need to perform this tutorial.

Creating a Database Connection

To create a database connection, perform the following steps:

1 . Open SQL Developer.

 

2 . In the Connections tab, right-click Connections and select New Connection.

Screenshot for Step

 

3 . Enter the following and click Test:

Connection Name: hr_orcl
Username: hr
Password: hr
Select Save Password checkbox
Hostname: localhost
Port: 1521
SID: orcl

Screenshot for Step

 

4 . The status of the connection was tested successfully. The connection was not saved however. To save the connection, click Connect.

Screenshot for Step

 

5 . Once the connection is saved, you will see the database in the list. When a connection is created, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created. Expand the hr_orcl connection.

Screenshot for Step

 

Browsing Your Database

The Connections Navigator in Oracle SQL Developer allows you to browse and edit database objects. This tutorial creates and debugs PL/SQL and uses a selection of tables from the HR schema. In this topic, you review the tables you will use later in the tutorial. Perform the following steps:

1 . Expand the Tables node.

Screenshot for Step

 

2 . Click the EMPLOYEES table to view the table definition.

Screenshot for Step

 

3 . To see the data, click the Data tab.

Screenshot for Step

 

4 . The Employee data is displayed. Click the DEPARTMENTS table in the navigator.

Screenshot for Step

 

5 . There are a number of constraints for the DEPARTMENTS table. Select the Constraints tab.

Screenshot for Step

 

6 . Click the Edit icon.

Screenshot for Step

 

7 . The dialog has a number of tabs, select the Foreign Keys tab.

Screenshot for Step

Review the Foreign Keys. Then click OK.

Screenshot for Step

 

8 . Verify that the JOBS and LOCATIONS tables exist, and have data, by selecting each in the Navigator in turn and reviewing the definitions and data.

 

Creating and Compiling a PL/SQL Procedure

In this topic you create, edit and compile a PL/SQL procedure. Perform the following steps:

1 . Right-click on the Procedures node in the Connections Navigator, to invoke the context menu, and select NEW PROCEDURE.

Screenshot for Step

 

2 . Enter EMP_LIST as the procedure name. Then click the + to add a Parameter. Double-click on the parameter name to allow you to change the value to pMaxRows and then change VARCHAR2 to NUMBER. Click OK.

Screenshot for Step

 

3 . The procedure is created.

Screenshot for Step

If the Grants tab is active, click the Code tab.

 

4 . Replace the following PL/SQL:

BEGIN
  NULL;
END EMP_LIST;

With the following code:
(This code is also in the file emp_cursor.sql in the directory where you unzipped the files from the Prerequisites. )

CURSOR emp_cursor IS
  SELECT l.state_province, l.country_id, d.department_name, e.last_name,
         j.job_title, e.salary, e.commission_pct
  FROM locations l, departments d, employees e, jobs j
  WHERE l.location_id = d.location_id
  AND d.department_id = e.department_id
  AND e.job_id = j.job_id;
  emp_record emp_cursor%ROWTYPE;
  TYPE emp_tab_type IS TABLE OF emp_cursor%ROWTYPE INDEX BY BINARY_INTEGER;
  emp_tab emp_tab_type;
i NUMBER := 1;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO emp_record;
  emp_tab(i) := emp_record;
  WHILE ((emp_cursor%FOUND) AND (i <= pMaxRows) LOOP
     i := i + 1;
     FETCH emp_cursor INTO emp_record;
     emp_tab(i) := emp_record;
  END LOOP;
  CLOSE emp_cursor;
  FOR j IN REVERSE 1..i LOOP
     DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name);
  END LOOP;
END;

Notice how the reserved words are formatted by Oracle SQL Developer. To format the code further, right-click to invoke the context menu and select Format SQL.
Compile the PL/SQL subprogram by clicking the Save button in the toolbar.

Screenshot for Step

 

5 . Compile errors, if any. are displayed.

Screenshot for Step

 

6 . By expanding Procedures on the navigator, EMP_LIST can be viewed.

Screenshot for Step

Note that when an invalid PL/SQL subprogram is detected by Oracle SQL Developer, the status is indicated with a red X over the icon for the subprogram in the Connections Navigator.

 

7 . Compilation errors are shown in the log window. You can navigate to the line reported in the error by simply double-clicking on the error. Oracle SQL Developer also displays errors and hints in the right hand gutter. If you hover over each of the red bars in the gutter, the error message displays.

In this case, the error messages indicate that there is a formatting error in the LOOP statement. After reviewing the code further, you see an extra parenthesis in the WHILE statement. Delete the extra parenthesis.

Screenshot for Step

 

8 . Click the Compile for Debug icon.

Screenshot for Step

 

9 . The procedure compiled successfully. You are now ready to run the procedure.

Screenshot for Step

Note: If you still see a red X over the icon for your procedure under the procedures node, click the refresh icon. A green overlay indicates the procedure has been compiled for debugging. No additional overlay means the procedure has been compiled without additional debugging directives. These are controlled by preference settings and the compile droplist option. The default in SQL Developer is “Compile for Debug”.

 

Running a PL/SQL Procedure

Once you have created and compiled a PL/SQL procedure, you can run it using Oracle SQL Developer. Perform the following steps:

1 . Right-click on EMP_LIST in the left navigator and select Run.

Screenshot for Step

 

2 . This invokes the Run PL/SQL dialog. The Run PL/SQL dialog allows you to select the target procedure or function to run (useful for packages) and displays a list of parameters for the selected target. In the PL/SQL block text area, you will see the generated code that Oracle SQL Developer uses to call the selected program. You can use this area to populate parameters to be passed to the program unit and to handle complex return types.

Screenshot for Step

Change PMAXROWS := NULL; to PMAXROWS := 5; Then click OK.

Screenshot for Step

 

3 . The results are displayed in the Running – Log window.

Screenshot for Step

 

Debugging a PL/SQL Procedure

Oracle SQL Developer also supports PL/SQL debugging with Oracle databases. In this topic, you debug a PL/SQL Procedure, step through the code and modify a value at runtime. Perform the following steps:

1 . To assist with debugging, line numbers can be added to the Code window. Click on the margin and a Toggle Line Numbers tip will appear. Select it.

Screenshot for Step

 

2 . Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the OPEN emp_cursor; statement. Then click the Debug icon.

Screenshot for Step

 

3 . The Debug PL/SQL dialog should still show the value PMAXROWS = 5; Click OK.

Screenshot for Step

 

4 . Click the Log tab, if it is not already displayed.

Screenshot for Step

 

5 . The debugger should halt at the line where you placed the breakpoint. You can now control the flow of execution, modify values of variables and perform other debugging functions. Click Step Into .

Screenshot for Step

Note: You have been granted DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges to avoid the following error message when debugging.

Screenshot for Step

 

6 . This takes you to the first line of the cursor. Click Step Into again.

Screenshot for Step

 

7 . You should now be selecting the first row of the cursor. Click Step Into 3 more times.

Screenshot for Step

 

8 . Click the Data tab.

Screenshot for Step

 

9 . The Data window starts to show a limited list of variables which are used in the line of code that is about to be executed, and in the previously executed line.

Screenshot for Step

 

10 . Right-click the line that reads DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name); and select Run to Cursor.

Screenshot for Step

 

11 . Expand emp_tab >_ values > [1] > _value. You see the values of the fields in a given record of the table. Select the LAST_<name> field.

Screenshot for Step

 

12 . Right-click the LAST_<name> field and select Modify Value.

Screenshot for Step

 

13 . Change the name to something else and click OK.

Screenshot for Step

 

14 . Select the Debugging – Log tab.

Screenshot for Step

 

15 . Click the Resume icon to allow the PL/SQL to run to completion.

Screenshot for Step

 

16 . Check to see that your modified value is displayed in the Log window.

Screenshot for Step

 

Summary

In this tutorial, you have learned how to:

  • Create a Database Connection
  • Browse the Database
  • Create and Compile a PL/SQL Procedure
  • Run a PL/SQL Procedure
  • Debug a PL/SQL Procedure

Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s