|
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
/*
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;
/
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.
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.
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?
|
