|
|
Topics: Hands-On 06 – Debugging a PL/SQL Stored Procedure In this Hands-On, you debug a procedure that is supposed to calculate the factorial of a number. You should pause and playback the movie as much as it is needed. In this procedure, it is supposed that if the input parameter is 3, then the program output be 6. (For example: 3! = 1*2*3) You have a logical problem in the procedure and it does not work. You have been assigned to investigate the problem. The Core Subject are: 1- Debugging a PL/SQL using Procedure Builder 2- Using Breakpoint 3- Using Stack 4- Compiling and running debug procedure 5- Test debug procedure 6- Introducing TEXT_IO package 7- PL/SQL Library
Manuscript Debug this procedure. It calculates the factorial of a number.
-- DEBUG_ME program before correction. PROCEDURE debug_me (p_num IN OUT NUMBER) IS /* This procedure finds the factorial of a number. n! = 1 * 2 * … * n-1 * n Pause this movie and read this procedure. If it’s input variable contains value 3, What is the output result? */ v_fact NUMBER; BEGIN --Loop
LOOP EXIT WHEN p_num = 0; v_fact := v_fact * p_num; p_num := p_num –1; END LOOP; -- Move the v_fact value into the p_num field. p_num := v_fact;
EXCEPTION
WHEN others THEN -- Remember this package is only works on client. /* If you move this procedure to a database server, your procedure will be invalid. And you need to change the TEXT_IO to DBMS_OUTPUT package. */
TEXT_IO.PUT_LINE(‘Other probllem’);
END;
= You can pause and playback this movie as many as you want. In this procedure, if your input is 3, then your output should be 6. We have a logical problem in this procedure. You job is to find what the problem is? Take your time; and look at the procedure.
Then, double click on the “test_debug_me” icon to open the run test procedure.
--TEST_DEBUG_ME program. PROCEDURE test_debug_me IS /* This is the variable, that is going to be the input and output of the debug_me procedure. */ v_check_me NUMBER; BEGIN v_check_me := 3; TEXT_IO.PUT_LINE(‘Before call program: ‘ || v_check_me); debug_me(v_check_me); -- What is the value of v_check_me? TEXT_IO.PUT_LINE(‘After call program: ‘ || v_check_me); END;
This procedure runs the “debug_me” procedure. It displays the “check_me” variable before and after calling the “debug_me” procedure. Read the procedure and then, choose the "Program" option and open the PL/SQL interpreter. === Run the “test_debug_me” procedure to see the output results. = The “After call procedure” line is not 6. We were expecting to have 6. The factorial of 3 is 6. Can you guess what the problem is? Take your time and read the procedure, why you are getting the wrong output. = Let’s debug the”debug_me” procedure. =
Click on the icon next to the “debug_me” procedure. Notice that it will move the procedure's source program to the PL/SQL interpreter’s source area. = Double click on line number 14 to make a "breakpoint." The big red dot is a "breakpoint" indicator. After defining the "breakpoint", you can run the “test_debug_me” procedure. = Notice that all the icons are gray. Type and run the test_debug_me procedure. Press the enter key. After the execution the icons are not gray anymore. The yellow arrow indicates the execution line. The procedure was executed up to line number 13 not 14. = On the stack section, expand "procedure body." Read the content of all variables. =
The "Step Into" icon takes me to the next line. Now, line number 14 was executed but not 15. (no changes on the v_fact variable)
The "Step Out" icon takes me to the next cycle of a breakpoint. Notice that the v_fact variable is not changing.
The "Reset" icon terminates the debug mode. Reset the process. Looks like the v_fact was not initialized. Since the v_fact variable is null, then all arithmetic calculations are going to be null. === Double click on the "debug_me" procedure to open the procedure. And initialize the v_fact variable. compile the procedure.
-- DEBUG_ME program before correction. PROCEDURE debug_me (p_num IN OUT NUMBER) IS /* This procedure finds the factorial of a number. n! = 1 * 2 * … * n-1 * n Pause this movie and read this procedure. If it’s input variable contains value 3, What is the output result? */ v_fact NUMBER; BEGIN
-- Assign one to the v_fact field. v_fact := 1;
-- Loop LOOP EXIT WHEN p_num = 0; v_fact := v_fact * p_num; p_num := p_num –1; END LOOP; -- Move the v_fact value into the p_num field. p_num := v_fact;
EXCEPTION
WHEN others THEN -- Remember this package is only works on client. /* If you move this procedure to a database server, your procedure will be invalid. And you need to change the TEXT_IO to DBMS_OUTPUT package. */
TEXT_IO.PUT_LINE(‘Other probllem’);
END;
= Go back to the "PL/SQL interpreter" to run the test_debug_me procedure to check the outcome of the changes.
The "after call program" line is 6. Looks like, the procedure returns the correct value. = Since the procedure is OK and passed the test, store the procedure in the PL/SQL library. Click and drag the procedure into “program units.” You can also keep track of the procedure's version. You can have multiple versions of the procedure. Well, since these versions are the same let’s delete them.
Notice that if you don’t have a duplicated procedure, no version will be assigned. = Try to store the procedure to the database server. Click and drag the procedure into “Stored Program Units.” The asterisk means: there is a compilation error. = Double click on the procedure icon to open it. Remember, the "text_io" package is used only on the local machine. You should use the "dbms_output" package on the server. = Click on the save icon to compile and close the window. No asterisk anymore. = Now, you should practice this over and over, until you become a master at it. Good Luck. |
|