Monday, November 16, 2020

Enable PL-SQL Debugger in Toad

 By default debugger is disabled in oracle 10/11g. The debug menu is grey out.

Resolution:

User need to have the ‘DEBUG CONNECT SESSION’ privilege granted. If not granted, the debugger icons will be disabled in the Procedure Editor.

Solution:

1. Grant debug any procedure to user_name;

2. Grant debug connect session to user_name;

3. GRANT EXECUTE ON DBMS_DEBUG to public;

( If DBMS_DEBUG is missing, Run it with SYS User)


GRANT EXECUTE ON DBMS_DEBUG to HR;

GRANT DEBUG CONNECT SESSION TO HR;

GRANT DEBUG ANY PROCEDURE TO HR;

   


CREATE OR REPLACE PROCEDURE debug_test

AS

  var NUMBER:=0;

BEGIN

  dbms_output.put_line('First step,  var = ' || var);

  var:=var+1;

  dbms_output.put_line('Second step, var = ' || var);

  var:=var+1;

  dbms_output.put_line('Third step, var = ' || var);

  var:=var+1;

  dbms_output.put_line('Fourth step, var = ' || var);

  var:=var+1;

  dbms_output.put_line('Fifth step, var = ' || var);

  var:=var+1;

 -- Final line stores the VAR value as 5. 

END;

/

There are two types of debugger  in toad.  DBMS Debugger and Script Debugger .

By Default dbms_debugger is greyed out and when user gets  the desired privilege thse options are activated .

 


1.Trace into :


 1.       Compile Referenced objects 




1.     2.  Set parameters


1.      3.  Pointer comes to 1st line before create or replace statement.




1.       4.Set watches (enable smart watch left pane ) by right click tool bar.




1.      5.  Click on Trace into continuously and see how the value of  the variable changing in watch.  

Trace into : à ‘Var ‘  Value is 0  : Line no .5 

.



Trace into : à ‘Var ‘  Value is 0  : Line No. 6



Trace into : à ‘Var ‘  Value is 1   : Line No.7

That means  The value increases after  execution of Var = Var+1 and when the pointer  moves

To next line. 



Hover the mouse on Highlighted line and the variable value will be displayed.



 

DBMS_OUTPUT Lines :

 

 

Fourth step, var = 3

Fifth step, var = 4

First step,  var = 0

Second step, var = 1

Third step, var = 2

Fourth step, var = 3

Fifth step, var = 4





The Last  Var = Var+1  holds Value 5 : line no. 14







No comments:

Post a Comment