Home > SQL Developer Tutorial: Cre... > Debug a PL/SQL Procedure
If you want to practice debugging a PL/SQL procedure with SQL Developer, create a procedure that is like the list_a_rating procedure that you created in Create a PL/SQL Procedure, but with a logic error. (The coding is also deliberately inefficient, to allow the display of the rating in a variable.)
Before you can debug the procedure, you must ensure that the user associated with the database connection has the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges.
To create this procedure, if you are not already connected, connect to the database as the user for the schema you are using for this tutorial. Right-click the Procedures node in the schema hierarchy on the left side, select New Procedure, and enter the following information using the Create PL/SQL Procedure dialog box.
Object Name: list_a_rating2
Click OK. A source window for the new procedure is opened. Enter (or copy and paste) the following procedure text, replacing any existing text:
CREATE OR REPLACE PROCEDURE list_a_rating2(in_rating IN NUMBER) AS matching_title VARCHAR2(50); matching_rating NUMBER; TYPE my_cursor IS REF CURSOR; the_cursor my_cursor; rating_cursor my_cursor; BEGIN OPEN the_cursor FOR 'SELECT title FROM books WHERE rating <= :in_rating' USING in_rating; OPEN rating_cursor FOR 'SELECT rating FROM books WHERE rating <= :in_rating' USING in_rating; DBMS_OUTPUT.PUT_LINE('All books with a rating of ' || in_rating || ':'); LOOP FETCH the_cursor INTO matching_title; FETCH rating_cursor INTO matching_rating; EXIT WHEN the_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(matching_title); END LOOP; CLOSE the_cursor; CLOSE rating_cursor; END list_a_rating2;
This procedure contains a logic error in the definition of the_cursor: it selects titles where the rating is less than or equal to a specified rating, whereas it should select titles only where the rating is equal to the specified rating.
Click the Save icon to save the procedure.
Assume that you wanted to run this procedure and list all books with a rating of 10. Right-click LIST_A_RATING2 in the Connections navigator hierarchy display and select Run. Under PL/SQL Block in the Run PL/SQL dialog box, change, change IN_RATING => IN_RATING to IN_RATING => 10, and click OK. In the Log window, however, you see unexpected output: many titles are listed, including some with ratings other than 10. So, you decide to debug the procedure.
To debug the procedure, follow these steps:
Click the Compile for Debug icon in the toolbar under the LIST_A_RATING2 tab.
Set two breakpoints by clicking in the left margin (left of the thin vertical line) beside each of these two lines:
FETCH the_cursor INTO matching_title; FETCH rating_cursor INTO matching_rating;
Clicking in the left margin toggles the setting and unsetting of breakpoints. Clicking beside these two lines will enable you to see the values of the matching_title and matching_rating variables as execution proceeds in debug mode.
Click the Debug icon, and in the Run PL/SQL dialog box change IN_RATING => IN_RATING to IN_RATING => 10; then click OK
Click View, then Debugger, then Data to display the Data pane. (Tip: Expand the Name column width so that you can see MATCHING_RATING.)
Press the F9 key (or click Debug, then Resume) to have execution proceed, stopping at the next breakpoint.
Repeatedly press the F9 key (or click Debug, then Resume), noticing especially the value of MATCHING_RATING as each row is processed. You will notice the first incorrect result when you see that the title Get Rich Really Fast is included, even though its rating is only 1 (obviously less than 10). (See the screen illustration with debugging information in Running and Debugging Functions and Procedures.)
When you have enough information to fix the problem, you can click the Terminate icon in the debugging toolbar.
From this debugging session, you know that to fix the logic error, you should change rating <= :in_rating
to rating = :in_rating
in the definition of the_cursor.
Related Topics
SQL Developer Tutorial: Creating Objects for a Small Database
Running and Debugging Functions and Procedures
Run/Debug/Profile PL/SQL (dialog box)