0) { obj.className = "imgborder_on"; } } } function hideImage(obj) { if (obj.className.substr(0, 10) == "imgborder_") { obj.src = eyeglass.src; obj.className = "imgborder_off"; } } function showAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { showImage(imgs[i]); } } function hideAllImages() { imgs = document.images; for (i=0; i < imgs.length; i++) { hideImage(imgs[i]); } } //-->

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:

 Overview
 Prerequisites
 Creating a Database Connection
 Browsing Your Database

Creating and Compiling a PL/SQL Procedure

 Running a PL/SQL Procedure
 Debugging a PL/SQL Procedure
 Summary

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.

 E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy
Left Curve
Popular Downloads
Right Curve
Untitled Document