As Published In
Oracle Magazine
November/December 2007

TECHNOLOGY: SQL Developer


Working with PL/SQL

By Sue Harper Oracle ACE

Edit, compile, run, and debug PL/SQL code easily with Oracle SQL Developer.

Among its other features, Oracle SQL Developer provides database developers and DBAs with a comprehensive environment for creating, editing, compiling, running, and debugging PL/SQL program units. Its editing capabilities include customizable syntax highlighting, bookmarks, code completion, code folding, and search/replace. The debugging features are especially useful for working with more-complex packages, debugging while stepping through a series of procedures, and remote debugging (debugging initialized by an external client or program).

This column introduces Oracle SQL Developer's key features for working with PL/SQL in the database. The column's examples use the sample HR schema and the EMP_FETCHER package—both available in the default database installation—and the EMP_REC object type, which you'll create. By working through the examples, you'll 

  • Run PL/SQL procedures, functions, and packages

  • Edit PL/SQL code

  • Compile PL/SQL

  • Debug compiled PL/SQL code, both locally and remotely


Getting Started

Follow these steps to establish the basic setup for this column's examples:

1. With an Oracle database running, start Oracle SQL Developer.
2. Create a new connection for the HR schema, and name it HR_ORCL . (For detailed information on creating a connection, see the link in "Next Steps.")
3. Open the SQL worksheet ( Tools -> SQL Worksheet ) for HR_ORCL . (It opens automatically when you first invoke the new connection.)
4. Enter the code in Listing 1 into the Enter SQL Statement text box.

Code Listing 1: Script for creating the EMP_REC object type

 

CREATE OR REPLACE TYPE EMP_REC AS
OBJECT(EMPLOYEE_ID NUMBER(6),   
 LAST_NAME VARCHAR2(25),   
 JOB_ID VARCHAR2(10),   
 MANAGER_ID NUMBER(6),   
 HIRE_DATE DATE,
 SALARY NUMBER(8, 2),
 COMMISSION_PCT NUMBER(2,2),
 DEPARTMENT_ID NUMBER(4));


5. Press F5 (or click the Run Script button) to create the EMP n _REC object type.
6. Click the Clear button or press Ctrl-D to clear the Enter SQL Statement text box.
7. Create the package specification and body, respectively, by entering the code in Listings 2 and 3 into the Enter SQL Statement text box and running the script for each. (Another way to create a new package is to expand the HR_ORCL node in the Connections Navigator, select Packages , and right-click to invoke the New Package ... menu.)

Code Listing 2: Script for creating the EMP_FETCHER package specification

 

CREATE OR REPLACE PACKAGE EMP_FETCHER AS
  FUNCTION GET_EMP(EMP_NO IN NUMBER) RETURN EMP_REC;


Code Listing 3: Script for creating the EMP_FETCHER package body

 

CREATE OR REPLACE PACKAGE BODY EMP_FETCHER AS
  FUNCTION GET_EMP(EMP_NO IN NUMBER) RETURN EMP_REC IS EMP_FOUND EMPLOYEES % ROWTYPE;
  EMP_RTN EMP_REC;
  BEGIN
    SELECT *
    INTO EMP_FOUND
    FROM EMPLOYEES
    WHERE EMPLOYEES.EMPLOYEE_ID = EMP_NO;
    EMP_RTN := EMP_REC(EMP_FOUND.EMPLOYEE_ID,   
                     EMP_FOUND.LAST_NAME,
                     EMP_FOUND.JOB_ID, 
                     EMP_FOUND.MANAGER_ID,
                     EMP_FOUND.HIRE_DATE,
                     EMP_FOUND.SALARY, 
                     EMP_FOUND.COMMISSION_PCT,
                     EMP_FOUND.DEPARTMENT_ID);
    RETURN EMP_RTN;
  END;
END;


To see the new package in the Connections Navigator, expand the HR_ORCL node and then expand the Packages node. Figure 1 shows the SQL worksheet and the Connections Navigator as they should look after you complete the preceding steps. 

Running PL/SQL Procedures, Functions, and Packages

To run any PL/SQL procedure, function, or package, you select the object in the Connections Navigator, right-click, and select Run . Now run the EMP_FETCHER package. The Run PL/SQL dialog box, displaying an anonymous PL/SQL block and a return value, appears. You'll see this dialog box whenever you run any PL/SQL in Oracle SQL Developer. It shows details about the arguments—and, for functions, return values—for the selected object. If the selected object is a package, the dialog box will display a list of the procedures and functions defined in the package specification. You can select one of these procedures or functions as the target you want to run.

In the PL/SQL block, change EMP_NO := NULL; to EMP_NO := 201; and click OK . In your EMPLOYEES table, 201 is an existing employee, so the package executes and completes with minimal feedback. For comparison, run the package again for EMP_NO := 2001; (a nonexistent employee). On completion, you should see a "no data found" error message.

In this example, the function retrieves information that a procedure could use. You can use the anonymous block in the Run PL/SQL dialog box to see that detail. The block contains the lines 

-- Modify the code to output the variable
 -- DBMS_OUTPUT.PUT_LINE('v_Return 
= ' || v_Return);

By uncommenting and modifying the second of these two lines, you can display the output. The function returns a record, or set of elements, so you must specify which value(s) you want to output. You can select from all the values in the record. This example uses the LAST_NAME, HIRE_DATE, and SALARY values. Expand the Types node in the Connections Navigator, and select EMP_REC . Review the code. Return to the EMP_FETCHER package, and run it again. Change EMP_NO := 2001 back to EMP_NO := 201 . Then replace the line 

--DBMS_OUTPUT.PUT_LINE('v_Return 
= ' || v_Return);


with
 

DBMS_OUTPUT.PUT_LINE('Employee '|| emp_no || ' is ' || v_return.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Hired on the '|| v_return.HIRE_DATE || ' and earns '|| v_return.salary);

Click OK , and view the output in the Running - Log window.

Editing and Compiling PL/SQL

To open Oracle PL/SQL Code Editor, expand the EMP_FETCHER package in the Connections Navigator and double-click the EMP_FETCHER Body node to open two new tabs. The first tab, which is read-only, is easily identifiable by a series of tabs along its top. The second tab is the code editor.

Oracle SQL Developer's Code Insight feature includes both Parameter Insight and Completion Insight. Parameter Insight displays a context-sensitive pop-up menu that provides you with a list of procedures or functions in a package. Completion Insight displays a context-sensitive pop-up menu that provides you with a list of possible completions at the insertion point, which you can use to autocomplete the code you are editing in either the code editor or the SQL worksheet. By default, if you type a period (.) and pause for more than one second, Completion Insight is invoked automatically. (You can change the delay time by setting a preference.) To invoke it manually, press Ctrl-space. To try Completion Insight, type SELECT HR . in Oracle PL/SQL Code Editor and then pause. Completion Insight provides a pop-up menu that displays all the objects HR owns. Click an object in the list to add it to your code at the insertion point.

Possibly more useful is a narrowed-down insight search, such as for the table alias. Type 

SELECT   FROM EMPLOYEES e 

in the code editor. Then type e in the space between SELECT and FROM and press Ctrl-space. After a pause, a list of the columns in the EMPLOYEES table pops up.

A full discussion of Oracle SQL Developer's editing features would be too extensive for this space. I encourage you to experiment with Code Insight and other Oracle PL/SQL Code Editor features, such as parenthesis matching (click on a set of parentheses, and note the highlighting), before compiling your code.

When you're ready to compile, click the Compile button or press Ctrl-Shift-F9. Any compilation errors display in the Compiler-Log window. Double-click on errors to navigate to the error source.

Debugging PL/SQL Code

Even if your PL/SQL code compiles successfully and runs, it might not perform exactly as you want it to. This is when you need to start debugging.

Next Steps



 LEARN more about Oracle SQL Developer

 FIND more PL/SQL examples and samples in the online documentation

 DISCUSS online documentation

 CREATE a database connection

To debug any code, you must set one or more breakpoints and then select Compile for Debug . This compiles the PL/SQL with PL/SQL library units for debugging. If you then run the code in debug mode, it runs to the first breakpoint. Breakpoints are customizable; for example, you can associate breakpoints with any unhandled exception or with specific Oracle Database exceptions. (If you are debugging PL/SQL in a database version prior to Oracle9 IDatabase Release 2, you must set the Migration -> PL/SQL Debugger preference to Use Probe Debugger to debug PL/SQL .)

Oracle SQL Developer's main debugging features let you control your program's execution. For example, you can step into each line of code or step over a procedure or function. Oracle SQL Developer displays the values of variables and data as you step though the code. Stepping over code is useful when you're debugging a procedure that calls a function. You don't necessarily want to go through all the lines of code in the function, so you step over it and back into the troublesome procedure.

Local and Remote Debugging with Oracle SQL Developer

Using Oracle SQL Developer to debug PL/SQL locally means that you select and connect to the PL/SQL program unit by using the SQL Developer Connections Navigator. You set the breakpoint where you want the debugger to pause, and then you click the Debug button. Oracle SQL Developer starts the debugging session, connects to it, and pauses when the breakpoint is reached. With local debugging, Oracle SQL Developer is the client that initiates debugging.

Remotely debugging PL/SQL with Oracle SQL Developer means that you initiate the debug action from a client external to Oracle SQL Developer. External clients include PL/SQL Web applications, Oracle Application Express applications, and SQL*Plus sessions. Remote debugging requires some manual steps: starting the Oracle SQL Developer debugger listener and attaching to that listener via the database session you want to debug. (Remote debugging is not available for databases prior to Oracle9 I Database Release 2.)

The following steps walk through an example of remote session debugging:

1. In Oracle SQL Developer, set up a connection to the database where the PL/SQL is resident. Use the same HR_ORCL connection and EMP_FETCHER package you used earlier.
2. Select the HR_ORCL connection in the Connections Navigator, and then right-click and select Remote Debug . This invokes the Listen for JPDA dialog box.
3. Enter a port and the IP address for the server. You should now see a new Run Manager window displaying the Debug Listener host information. (You can set Tools -> Preferences -> Debugger -> Prompt for Debugger Host for Database Debugging when connecting across firewalls or virtual private network [VPN] sessions.)
4. In the Connections Navigator, select the EMP_FETCHER package body, and then right-click and select Edit... to open Oracle PL/SQL Code Editor. Click in the left margin next to FUNCTION GET_EMP... to set a breakpoint.
5. Click Compile for Debug , as shown in Figure 2. 6. You are now ready to start the remote debugging process from Oracle SQL Developer's perspective. You need to get the external client—in this case SQL*Plus—ready. Start a SQL*Plus session for the same database user, and enter the following, substituting the IP address and port you used when you started the remote connection from Oracle SQL Developer: 

exec DBMS_DEBUG_JDWP.CONNECT_TCP ('127.0.0.1', 4000)

 

figure 1
Figure 1: Connections Navigator and SQL worksheet
figure 2
Figure 2: Compile for debug

7.
In SQL*Plus, use an anonymous block to call the function:

 

DECLARE
  EMP_NO NUMBER;
  v_Return HR.EMP_REC;
 BEGIN
  v_Return := EMP_FETCHER.GET_EMP(201);
 END;
 /


8. Control now passes to Oracle SQL Developer, and you can step through the code. The process at this point is the same for remote debugging as for local debugging.

Various debug windows are now active. They all have a role, but the immediately important ones are 

  • The Debugging window, which includes the program execution controls. Start debugging, by clicking the Step Into icon. 

  • The code editor, which shows the execution point. As you step through the code, mouse flyover tool tips display the name and value of the variable under the pointer. Keep clicking Step Into until you reach the line EMP_RTN:= EMP_REC.... At this point, let your mouse hover over one of the EMP_FOUND values to see the tool tip, as shown in Figure 3. 

  • The Data window displays all variables that are currently in scope. Still with the execution point at EMP_RTN := EMP_REC..., select the Data tab and expand the EMP_FOUND node. The record is now populated. At this point, you can watch and modify variables to see the impact on your procedure.

    figure 3
    Figure 3: Using Step Into and tool tip information


9. Continue to step through the code until your procedure completes, control is returned to the external client, and the debug session is finished.

Conclusion

With Oracle SQL Developer, you can browse and edit database objects, query data, and run reports. One of the useful features in Oracle SQL Developer is its ability to edit, compile, run, and debug PL/SQL. Oracle SQL Developer supports both local and remote debugging; the remote-debugging capabilities let you kick off PL/SQL debugging sessions inside Oracle SQL Developer from external clients (such as SQL*Plus).

 



Sue Harper
(sue.harper@oracle.com) is an Oracle senior principal product manager based in London.

 

Send us your comments