Developing and Debugging PL/SQL using Oracle SQL Developer

Open SQL Developer.

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

Enter the following and click Test:

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

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

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.

Expand the Tables node.

Click the EMPLOYEES table to view the table definition.

To see the data, click the Data tab.

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

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

Click the Edit icon.

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

Review the Foreign Keys. Then click OK.

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.

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

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.

The procedure is created.

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

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.

Compile errors, if any. are displayed.

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

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.

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.

Click the Compile for Debug icon.

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

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".

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

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.

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

The results are displayed in the Running - Log window.

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.

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.

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

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

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 .

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

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

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

Click the Data tab.

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.

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

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.

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

Change the name to something else and click OK.

Select the Debugging - Log tab.

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

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