Sponsored Ad

Tuesday, March 30, 2010

Oracle Procedure Unit Testing Using PL/SQL Developer

This post will help developers and unit testers to test oracle procedure using PL/SQL Developer.

Lets start step by step procedure to unit test a procedure.

I am assuming that you already have a PL/SQL developer and a procedure created in it.

I have a Procedure called PKG_Test and it have two input parameters and a output cursor.

Step 1: just open a browser window in PL/SQL Developer by Checking Tools-> Browser option.

browser window in PL/SQL Developer


Step 2: Search for procedure and right Click on it and click the View Spec and Body option.

View Spec and Body option PL/SQL


Step 3:  Now Package is open in new window just right click on procedure name and click on test option in menu.

Package window PL/SQL


Step 4: Test window opened now to start unit testing (Debugging) just enter the input parameter if any and click on Start Debugger button or F9.

Start Debugger button


test window PL/SQL


Step 5: Now cursor goes inside the procedure block . there is options to stop testing execute all blocks and step over and step out.

Let me explain all things clearly.


image Run button(CTRL R) will run the program till next break point, if no break point is there, it will just execute all code.

image Step into (CTRL N) will go inside the functions or another procedure, if any.

image Step Over(CTRL O) will move the debugger control to next executable statement.

image Step Out (CTRL T) will move the debugger control to the parant function or procedure.

image Run to Next Exception will run the control to next exception otherwise break the debugging.

PL/SQL Debug window

you can test the variable values at any point of debugging time, just go to Script tab and insert in the variable name under variable column and you will get current value of the variable under value column.

This program is just to demonstrate the debugging process in PL/SQL developer, we haven't used any passed parameter in this. you can use as per your requirement.

PL/SQL check variable values


Step 6:  Now procedure run is over and you can check the results at Cursor … icon.

Once the debugging is complete you can check the results of procedure in test window cursor column.


PL/SQL Results

Step 7: A new result window opened and you can see the results in window.

This procedure of block is returning 3 rows which contains the 'c' character in name column.

result window PL/SQL

Now you can debug the complex procedures. Happy testing.

No comments:

Post a Comment

Sponsored Ad

Development Updates

Tech Updates