Developing and Debugging PL/SQL using Oracle SQL Developer

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

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 3.0 from OTN. Follow the readme instructions here.

.

Install Oracle Database 11g with the Sample schema.

.

Unlock the HR user. Login to SQL Developer as the SYS user and execute the following commands:
alter user hr identified by hr account unlock;
grant debug connect session to hr;
grant debug any procedure to hr

Note: This tutorial is developed using Oracle SQL Developer 3.0. However, you can also use Oracle SQL Developer 2.1.1.

.

Download and unzip the files.zip to a local folder on your file system. In this tutorial, we use the C:\sqldev3.0 folder.

Creating a Database Connection

The first step to managing database objects using Oracle SQL Developer 3.0 is to create a database connection. Perform the following steps:

.

If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Developer and move to Step 4. If you do not have the icon located on your desktop, perform the following steps to create a shortcut to launch SQL Developer 3.0 directly from your desktop.

Open the directory where the SQL Developer 3.0 is located, right-click sqldeveloper.exe (on Windows) or sqldeveloper.sh (on Linux) and select Send to > Desktop (create shortcut).

 

.

On the desktop, you will find an icon named Shortcut to sqldeveloper.exe. Double-click the icon to open SQL Developer 3.0.

Note: To rename, select the icon and then press F2 and enter a new name.

 

.

Your Oracle SQL Developer opens.

 

.

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

 

.

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: HR_ORCL
Username: hr
Password: <your_system_password>
Hostname: localhost
Port: 1521
SID: <your_SID>

 

.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Connect. Then click Save.

 

.

The connection was saved and you see the newly created connection in the Connections list.

When a connection is created, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you have opened. Expand the HR_ORCL connection.

 

Creating and Compiling a PL/SQL Procedure

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

.

Right-click Procedures node in the Connections navigator, to invoke the context menu, and select New Procedure.

 

.

Enter EMP_LIST as the procedure name and then click to add a parameter. Double-click Parameters name to allow you to change the value to pMaxRows. Change the type from VARCHAR2 to NUMBER. Click OK.

 

.

The procedure is created.

Note: At this point, only the shell of the procedure is completed. In the next step, you add more PL/SQL code into the procedure.

 

.

Replace the following PL/SQL:

BEGIN
  NULL;
END EMP_LIST;

with the following code:

(Note: This code is in the file emp_cursor.sql in the directory where you unzipped the files from the Prerequisites section.)

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 within the code editor to invoke the sub menu and select Format.

Compile the PL/SQL subprogram by clicking Save 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 Compile.

 

.

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

 

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:

.

Right-click on EMP_LIST in the Connections 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.

In your EMP_LIST procedure, you have a parameter named PMAXROWS. In the Run PL/SQL dialog, you can initialize that parameter to any number value.

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

 

.

The results are displayed in the Running - Log window.

 

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:

.

To assist with debugging, line numbers can be added to the Code window. Right-click on the margin and select Toggle Line Numbers.

 

.

To debug a procedure, you need to Compile for Debug first. This step adds in the compiler directives required for debugging. Once you have completed the debug, you should compile the procedure again and remove the extra directives.

 

.

A breakpoint is a location in the code that you identify as a stopping point. When code is run in debug mode, execution will stop at the breakpoint.

Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the OPEN emp_cursor; statement. The line number is replaced with a red dot. This is a breakpoint symbol.

Then click the Debug icon.

 

.

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

 

.

Click 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 the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges in the Prerequisites section 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.

 

.

Select Data from the tab above.

 

.

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

 

.

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 another value, such as James, and click OK.

 

.

Select the Debugging - Log tab.

Note that you have changed the value of the variable at run time. This is very helpful in debugging code.

 

.

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

 

.

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

 

Summary

In this tutorial, you have learned how to:

Hardware and Software Engineered to Work Together About Oracle |Oracle and Sun | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Your Privacy Rights