iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |

 

Lesson 12

“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?