TECHNOLOGY: SQL Developer
Working with PL/SQLBy Sue Harper
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 packageboth available in the default database installationand the EMP_REC object type, which you'll create. By working through the examples, you'll
Follow these steps to establish the basic setup for this column's examples:
1. With an Oracle database running, start Oracle SQL Developer.
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.
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 argumentsand, for functions, return valuesfor 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);
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.
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.
exec DBMS_DEBUG_JDWP.CONNECT_TCP ('127.0.0.1', 4000)
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
9. Continue to step through the code until your procedure completes, control is returned to the external client, and the debug session is finished.
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 (firstname.lastname@example.org) is an Oracle senior principal product manager based in London.