One of the significant features of Oracle’s SQL Developer client is the built-in debugger for PL/SQL stored objects: PL/SQL stored procedures, stored functions, and triggers. In this tutorial we describe how to use the SQL Developer 4.1.0 debugger with an Oracle 12.0.1 server. Setting up the correct permissions and authorizations so that development staff can use the tool is more complex with Oracle 12 than with Oracle 11g, and I will go into some of the gory detail near the end of this article. Nonetheless – here we go.
Step 1: Create a PL/SQL function or procedure. Consider this simple example, “SQUARE”, that returns the square of the given NUMBER value:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
CREATE OR REPLACE FUNCTION SQUARE( X_IN IN INTEGER ) RETURN INTEGER IS result INTEGER := 0; BEGIN SELECT POWER( X_IN, 2.0 ) INTO result FROM dual; RETURN result; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Value: ' || TO_CHAR(X_IN) || ' caused an overflow error'); RETURN result; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An unknown exception occurred in function SQUARE'); RETURN result; END SQUARE;
Note the existence of SQUARE in the left sidebar of the SQL Developer client:
Step 3: Compile the SQUARE function in debug mode, by right-clicking the SQUARE function and selecting “Compile for Debug” from the pop-up menu.
Step 4: Right-click again on the SQUARE function in the left sidebar and choose the “Edit” menu item. This creates a new edit window within the SQL Developer client containing just the code of this procedure.
Step 5: Within the editor window for the SQUARE function, right-click in the line number area to toggle a suitable breakpoint – for example, one could set a breakpoint on the SELECT statement that computes the square of the result:
Step 6: Within SQL Developer, open another SQL window, and execute the following procedure:
Note that the brackets () here are mandatory. If you had enabled the DBMS_OUTPUT pane, you’ll see the result of the procedure in that window, which verifies that your Java access control for debugging has been enabled for your userid and current IP address:
You can execute the ADMIN.ENABLE_DEBUGGING() procedure at any time prior to starting your use of the SQL Developer debugging tools. See the “Oracle 12c Debugging Setup” section at the end of this post for details of the ENABLE_DEBUGGING() procedure.
Step 7: Return to the SQL window for the SQUARE() function. Click on the “ladybug” in the menu bar above the source code, which displays this dialog:
The dialog is a debugging wizard that presents you with a PL/SQL program to invoke the SQUARE function with the value “1”. If you click “OK” you will execute the program, which will invoke the SQUARE function and halt execution at the breakpoint located within the function at line 5 (the SELECT statement). The SQL Developer client will look like this:
Step 8: To control execution, you can use the command icons located just above the debugging log at the bottom of the SQL Developer window, or you can use the following function keys:
- Resume: F9
- Step out: SHIFT-F7
- Step info: F7
- Step over: F8
As with other debuggers you can use the Smart Data and Data panes to view the values of variables at any step through the function as it executes:
Step 9: If you press F8 several times in succession, you’ll exit the SQUARE function and return to the anonymous PL/SQL block that was generated for you by the DEBUG wizard – the PL/SQL block is now in a separate pane in the SQL Developer editor:
Now you can step through the anonymous block and verify that SQUARE actually returned the correct value (1).
Step 10. To debug your own anonymous PL/SQL block, then, one merely has to create a PL/SQL function or procedure that your anonymous block calls – and once the debugger returns from that function or procedure, one can debug your PL/SQL code in the anonymous block to verify its execution semantics. Note: there can be an issue when replacing the debugger wizard anonymous block code. See the last section of this article.
The simplest, most straightforward PL/SQL stored procedure is the example below, which I’ve called
16 17 18 19 20
CREATE OR REPLACE PROCEDURE DO_NOTHING IS BEGIN NULL; END;
DO_NOTHING for debug mode using SQL Developer pane by
DO_NOTHINGin your list of procedures;
- right click; and
- select Compile With Debug
You can then open the
DO_NOTHING procedure in a separate pane in SQL Developer, and set a breakpoint on the NULL statement within the
To debug an anonymous block, insert a statement within the anonymous block’s BEGIN section that calls
DO_NOTHING(), after ensuring that
DO_NOTHING is first compiled in debug mode. You can, for example, cut-and-paste your anonymous block into the debugging wizard once you click on the “ladybug” icon within SQL Developer. Stepping out of the
DO_NOTHING procedure when you are within the debugger will permit you to debug your anonymous block.
Debugger Setup with Oracle 12c
To permit a user to use the debugger from the SQL Developer client, the user must have the following privileges:
21 22 23
GRANT DEBUG CONNECT SESSION TO <username-or-role>; GRANT CREATE PROCEDURE TO <username-or-role>; GRANT EXECUTE ANY PROCEDURE TO <username-or-role>;
The latter privilege isn’t strictly necessary – it can be done via specific GRANT statements to the required procedures – but such fine-grained access restrictions makes little sense for developers, particularly in a test environment. In addition, you must enable debugging via Java for your specific userid and IP address, a new restriction imposed by Oracle 12c that was not required with Oracle 11g. This is the reason for Step 6 above, which is the call to the ADMIN.ENABLE_DEBUGGING() procedure. The procedure contents is as follows:
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
CREATE OR REPLACE PROCEDURE ENABLE_DEBUGGING IS ip_address VARCHAR2(40); BEGIN ip_address := SYS_CONTEXT( 'userenv', 'ip_address' ); DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (HOST=>ip_address, ace=> SYS.XS$ACE_TYPE(privilege_list=>sys.XS$NAME_LIST('JDWP') , principal_name=>USER, principal_type=>sys.XS_ACL.PTYPE_DB) ); DBMS_OUTPUT.PUT_LINE( 'Added Java debugging privilege for user: ' || USER ); DBMS_OUTPUT.PUT_LINE( 'At IP address: ' || ip_address ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 'ENABLE_DEBUGGING: An unknown exception has occurred.' ); RAISE; END;
The “ADMIN” account referred to above, that creates the ENABLE_DEBUGGING() procedure, has SYSDBA privileges. Note that authorization to the package DBMS_NETWORK_ACL_ADMIN is granted to the DBA role; to embed the call to DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE() within a stored procedure, the SYS account for the database must explictly
GRANT EXECUTE ON DBMS_NETWORK_ACL_ADMIN TO ADMIN. The procedure, then, permits developers (in my case, students) to add themselves to the server’s Access Control List on demand, and it works even if the student-developers have signed in from remote locations over VPN.
If the ENABLE_DEBUGGING() procedure has been called and is working correctly, when you invoke the debugger by clicking OK on the debugging wizard dialog, you should see the following lines in the debugging log that will appear on your screen (the IP address and port number will differ):
41 42 43
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP('10.191.59.133', '50504') Debugger accepted connection FROM DATABASE ON port 3480.
If the ENABLE_DEBUGGING() procedure has not worked correctly, you will see an error similar to the following:
44 45 46 47 48 49 50 51 52
Connecting TO the DATABASE Oracle IT Server - gpaulley. Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP('10.191.59.133', '50504') ORA-24247: network ACCESS denied BY ACCESS control list (ACL) ORA-06512: AT "SYS.DBMS_DEBUG_JDWP", line 68 ORA-06512: AT line 1 Process exited. Disconnecting FROM the DATABASE Oracle IT Server - gpaulley.
If you encounter error ORA-24247 after executing the ENABLE_DEBUGGING() procedure, please contact your instructor.
Debugging your own anonymous block
If you wish, you can replace the anonymous block generated by the SQL Developer debugger completely with your own anonymous block code – as long as it calls a compiled-for-debug PL/SQL procedure or function, such as DO_NOTHING, that has a breakpoint set, you will be able to debug your own anonymous block code as if it was a PL/SQL stored function or procedure.
There is a glitch, however, in the current implementation of the debugging wizard in the SQL Developer 4.1.0 and 4.1.1 clients. Currently, the SQL Developer client requires the anonymous block to assign a value to a specific “bind variable”, v_Return, within the block. For example, the default code generated to debug the SQUARE stored function looks like this:
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
DECLARE X_IN NUMBER; v_Return NUMBER; BEGIN X_IN := 1; v_Return := SQUARE( X_IN => X_IN ); /* Legacy output: DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return); */ :v_Return := v_Return; --rollback; END;
Note the assignment to the special bind variable :v_Return on line 65. This variable must be given a value in your anonymous block to prevent the debugging session from terminating with an obscure error, “Invalid Column Index”, a known problem documented here. From that bug report comes the following description:
This error seems to happen if you are debugging a function (or any subprogram with OUT parameters) and the debug call block does not assign the function’s return value (and/or OUT parameters) to bind variable.
The default call block generated by SQL Developer for debugging a function ends with a statement like:
:v_Return := v_Return;
assigning the return value from the function call to a bind variable.
If you remove this bind variable entirely (say, because you used the “From File” option to load some existing script for calling the function in question), then you get the “Invalid Column Index” error. Presumably SQL Developer expects to be able to get the return value of the function out of a bind variable, so if there is nothing to bind to it gets an error.
Now, there doesn’t seem to be any very good reason for this, since as far as I can tell you can’t view the return value in any way after completing the debug run (and in fact, in many debug runs you may not even run the function to completion).
If you explicitly assign a value – any value – to the bound variable :v_Return then your anonymous block debugging session should work without error.