 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
Topics
This tutorial covers the following topics:
Viewing Screenshots
Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.
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.
Back to Topic List
Prerequisites
Before you perform this tutorial, you should:
| 1.
|
Install
Oracle Database 11g
Note: You can use any Oracle Database above 9.2.0.1
|
| 2.
|
If you are not using Oracle Database 11g, install Oracle SQL Developer 1.5.x.
Note: Oracle SQL Developer is available for download for FREE from
OTN. To install Oracle SQL Developer, unzip it into any directory on your machine.
|
| 3. |
Download and unzip
plsql.zip
into your working directory (i.e. d:\wkdir)
|
| 4.
|
Have access to the shipped
HR schema.
If not already done, a DBA user needs to unlock the schema to provide access. This can be done with the following commands:
ALTER USER hr ACCOUNT UNLOCK;
ALTER USER hr IDENTIFIED BY hr;
|
Back to Topic List
Creating a Database Connection
To create a database connection, perform the following steps:
| 1.
|
Open Windows Explorer and double-click
<your_path>\sqldeveloper\sqldeveloper.exe
.
Note: If you receive a dialog window asking whether you want to migrate settings from a previous release, click
No.
|
| 2.
|
In the Connections tab, right-click
Connections and select
New Connection.
|
| 3.
|
Enter
HR_ORCL
for the Connection Name (or any other name that identifies your connection),
HR
for the Username and Password, specify your
<hostname>
for the Hostname and enter
ORCL
for the SID. Click
Test.
|
| 4.
|
The status of the connection was tested successfully. The connection was not saved however. To save the connection, click
Connect.
|
| 5.
|
Once the connection is saved, you will see the database in the list. Expand
HR_ORCL.
|
| 6.
|
When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.
|
Back to Topic List
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.
|
| 2.
|
Click the
EMPLOYEES table to view the table definition.
|
| 3.
|
To see the data, click the
Data tab.
|
| 4.
|
Click the
DEPARTMENTS table in the navigator.
|
| 5.
|
There are a number of constraints for the DEPARTMENTS table. When the table definition displays, select the
Constraints tab.
|
| 6.
|
Click the
Edit icon.
|
| 7.
|
The dialog has a number of tabs, select the
Foreign Keys tab.
Review the Foreign Keys. Then click
OK.
|
| 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.
|
Back to Topic List
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.
|
| 2.
|
Enter
EMP_LIST as the procedure name. Then click the
+ to add a Parameter.
|
| 3.
|
Double-click on the parameter name to allow you to change the value to
pMaxRows and then change VARCHAR2 to
NUMBER. Click
OK.
|
| 4.
|
The skeleton of the procedure with the parameter specified is displayed.
|
| 5.
|
Replace the following PL/SQL:
BEGIN
NULL;
END EMP_LIST
;
With the following code:
(This code is also in the
plsql.zip
into your working directory (i.e. d:\wkdir) in the file
emp_cursor.sql)
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.
|
| 6.
|
By expanding
Procedures on the navigator, EMP_LIST can be viewed.
|
| 7.
|
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.
|
| 8.
|
Click the
Compile icon.
|
| 9.
|
The procedure compiled successfully. You are now ready to run the procedure.
|
Back to Topic List
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.
|
| 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.
Change
PMAXROWS :=
NULL;
to
PMAXROWS :=
5;
Then click
OK.
|
|
3
.
|
The results are displayed in the Running - Log window.
|
Back to Topic List
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.
|
| 2.
|
Click
Edit on the toolbar.
|
| 3.
|
Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the
OPEN emp_cursor; statement.
|
| 4.
|
Click the
Debug icon.
|
| 5.
|
The Debug PL/SQL dialog should still show the value
PMAXROWS = 5; Click
OK.
|
| 6.
|
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.
Note: If at this point you receive the error message "This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges", you need to complete Step 4 of
Prerequisites.
|
| 7.
|
Click
Step Into
.
|
| 8.
|
This takes you to the first line of the cursor. Click
Step Into
again.
|
| 9.
|
You should now be selecting the first row of the cursor. Click
Step Into
3 more times.
|
| 10.
|
Click the
Smart
Data tab.
|
| 11.
|
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.
|
| 12.
|
Right-click the line that reads
DBMS_OUTPUT.PUT_LINE(emp_tab(j).last_name); and select
Run to Cursor.
|
| 13.
|
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.
|
| 14.
|
Right-click the
LAST_NAME field and select
Modify Value.
|
| 15.
|
Change the name to something else and click
OK.
|
| 16.
|
Select the Debugging - Log tab.
|
| 17.
|
Click the Resume icon
 to allow the PL/SQL to run to completion.
|
| 18.
|
Check to see that your modified value is displayed in the Log window.
|
Summary
In this lesson, you 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
|
Back to Topic List
Place the cursor over this icon to hide all screenshots.
|
testcontent
Printer View