"To fear love is to
fear life, and those who fear life are already three parts dead."
Bertrand Russell (1872 - 1970), Marriage and Morals (1929) ch.
19 |
Read
first then play the video:
PLS-VIDEO -Debugging
PL/SQL
Section 2: Debugging PL/SQL
Debugging PL/SQL Stored
Procedure
Hands-On introduction
In this Hands-On, you debug
a procedure that is supposed to calculate the factorial of a number.
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.
Debug this procedure.
Let us to assume that you
are going to debug the following PL/SQL procedure. This procedure
calculates the factorial of a number.
Debugging procedure
-- DEBUG_ME program before
correction.
PROCEDURE debug_me
(p_num IN OUT NUMBER)
IS
--your comments
/*
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?
*/
--define
variable
v_fact NUMBER;
BEGIN
--Loop
LOOP
EXIT WHEN p_num = 0;
--multiple v_fact by p_num
v_fact := v_fact * p_num;
--subtract one from 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;
/
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
--your comments
/* This is the variable,
that is going to
be the input and output of
the debug_me procedure. */
--define
variable
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.
Notice that 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.
If you are not able to fix
the bug, 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.
Create a BREAKPOINT line
Double click on line number
that contains the "v_fact := v_fact * p_num;" statement to make a
"BREAKPOINT." Let us assume that it is line 14.
The big red dot is a
"breakpoint" indicator. After defining the
"breakpoint", you can run the "test_debug_me" procedure.
PL/SQL> test_debug_me;
Icons in the PL/SQL debug
window
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
the prior line but not 14.
Step Into icon
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)
Step Out icon
The "Step Out"
icon takes me to the next cycle of a breakpoint. Notice that the
v_fact variable is not changing.
Reset icon
The "Reset" icon
terminates the debug mode. Reset the process. It looks like the v_fact
was not initialized. Since the v_fact variable is null, then all
arithmetic calculations are going to be null.
Stack Section
On the stack section,
expand "procedure body." Read the content of all variables.
Correct the error
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;
/
Compile and save the PL/SQL
procedure.
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.
Save a PL/SQL procedure in
the PL/SQL library
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 keep track of the
procedure's version. Also, you can have multiple versions of the
procedure. Notice that if you don"t have a duplicated procedure, no
version will be assigned.
Save a PL/SQL procedure in
the Database server.
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.
TEXT_IO vs. DBMS_OUTPUT
package
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.
Change the statement and
click on the save icon to compile and close the window. You should not
see anymore asterisk.
"Happiness is
nothing more than good health and a bad memory." Albert
Schweitzer (1875 - 1965) |
Questions:
Q: How do you debug a
PL/SQL procedure?
Q: How do you move a PL/SQL
procedure to the PL/SQL interpreter"s source area?
Q: What is the BREAKPOINT
indicator in the PL/SQL interpreter?
Q: How do you create a
BREAKPOINT in the PL/SQL interpreter?
Q: How do you activate the
Step Into, Step Out, and Reset icon in the PL/SQL interpreter?
Q: What does the Step Into
icon in the PL/SQL interpreter?
Q: What does the Step Out
icon in the PL/SQL interpreter?
Q: What does the Reset icon
in the PL/SQL interpreter?
Q: What does the STACK
section contain?
Q: How can you see the
columns and variables values in the PL/SQL program using the PL/SQL
interpreter?
Q: How do you store a
PL/SQL procedure in the PL/SQL library?
Q: Can you have multiple
versions of a PL/SQL procedure in the PL/SQL library?
Q: How do you store a
PL/SQL procedure in your database server?
Q: How can you copy a
PL/SQL procedure to your database server?
Q: What would be happen if
you move or copy a locally PL/SQL procedure with its local packages
into the database server?
|