Đăng bởi: daoquanghuynh | 31/03/2010

Developing and Debugging PL/SQL using SQL Developer


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


Oracle SQL Developer is a new, 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.

The first production release, Oracle SQL Developer 1.0 is now available for download for Windows, Linux and Mac OS X. Oracle SQL Developer can connect to any Oracle Database version and later.


Oracle SQL Developer was developed in Java leveraging the Oracle JDeveloper IDE framework. Default connectivity to the database is through the JDBC Thin driver (no Oracle Home required); the JDBC Type 2 driver (OCI client side driver) is also supported. There is a separate distribution available bundled with JRE 1.5, with an additional tools.jar to support Windows clients. Non-Windows clients only need JDK 1.5. Installation is performed simply by unzipping the downloaded file. Oracle supports the Windows, Linux and Mac OS X platforms.

Back to Topic List


Before starting this tutorial, you should:

1. Have access to an Oracle Database 10g instance or perform the Installing Oracle Database 10g on Windows tutorial. Note: you can also perform this tutorial connecting to a non-windows database.
2. Download Oracle SQL Developer from OTN and unzip it into any directory on your machine.
3. If not already done, a DBA user needs to unlock the schema to provide access. This can be done with the following commands:

4. For the PL/SQL debugging portion of this tutorial, the HR user needs a few additional privileges. Grant the privileges using the following commands:

GRANT debug any procedure, debug connect session TO 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: you can also execute sqldeveloper in a non-windows environment.

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

3. Enter HR_<your_db_sid_name> 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 <your_db_sid> for the SID. Then click Test.

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

5. The connection was saved and you see the database in the list. Expand <your_database_connection>.

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 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 Create PROCEDURE.

2. Enter EMP_LIST as the procedure name. Then click the + to add a Parameter.

3. Double-click on param to allow you to change the value to pMaxRows and then change VARCHAR2 to NUMBER. Make sure you press enter before you click OK.

4. The skeleton of the procedure with the parameter specified is displayed.

5. Replace the following PL/SQL:


With the following code:

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;
  emp_tab emp_tab_type;
i NUMBER := 1;
  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;
  CLOSE emp_cursor;

Notice how the code is automatically formatted by SQL Developer. Compile the PL/SQL subprogram by clicking the Save button in the toolbar.

6. Expand Procedures in the navigator.

7. Note that when an invalid PL/SQL subprogram is detected by SQL Developer, the status is indicated with a red X over the icon for the subprogram in the System 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. 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 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 is generated code that 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 of the 5 rows returned are displayed in the log window.

Back to Topic List

Debugging the PL/SQL Procedure

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. Set a breakpoint in the EMP_LIST procedure by clicking in the margin at the line with the OPEN emp_cursor; statement.

2. Click the Debug icon (ladybug).

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

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

5. Click Step Into .

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

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

8. The Smart 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.

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

10. Expand emp_tab > values > [1] > _key. You see the values of the fields in a given record of the table. Select the LAST_NAME field.

11. Right-click the LAST_NAME field and select Modify Value.

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

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

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


Chuyên mục

%d bloggers like this: