Plsql stored procedure execution - how to monitor

Question

How to monitor stored procedure execution in toad ? Summary of what I'm trying to monitor:

Procedure ArchiveData ( Param 1, Param 2, Param 3, Param 4)(

    Variable 1 := Some calc using above params;
    Variable 2 := Some calc using above params;

Call_to_another_procedure2(Param3, Param 4,variable 1);

Call_to_another_procedure3(Param3, Param 4,variable 1);

End;

Is there away to look at all the statements executed with the param values including the statements in the called procedure/functions ?

I'm using Toad 14.1
Thanks for your help!

Tried using session browser and sql Monitor but that is not helpful

Answer 1

The typical way of debugging PL/SQL is to emit messages via dbms_output.put_line, which you can see in most database client software products, though sometimes you have to enable it in settings/options and it may appear in a separate tab or screen than your results normally do. Internally those products are simply calling dbms_output.get_line in a loop and displaying the output to you until there isn't any more in the buffer. You load the buffer in PL/SQL by calling put_line.

DECLARE
  var1 ...
  var2 ...
BEGIN
  dbms_output.put_line('var1 = '||var1||', var2 = '||var2);
  procedurecall(var1,var2);
  dbms_output.put_line('Got here 2');
  --etc...
END;

The second thing, which is actually more important, is good exception handling. You should handle exceptions close to the points of potential error and always make sure you either re-raise to the client or report those errors (dbms_output, logging table, etc..) with the full error stack and call stack. The error stack is the most important, as it will tell you the exact line number where the exception was thrown. Usually if an exception is thrown to the client your client will give you the whole error stack, and you have what you need. If not, you can obtain it yourself.

Example:

BEGIN
  -- do something that can fail
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(dbms_utility.format_error_backtrace||CHR(10)||
                         dbms_utility.format_error_stack||CHR(10)||
                         dbms_utility.format_call_stack);
    RAISE;
END;

Of course you can use exception handling in other ways as well, like not reraising back to the client but moving forward or returning a status code instead of an exception, etc.. but this should give you an idea. The goal is to always know where something failed, at what line number, and where in your PL/SQL program and what stack of exception handlers re-raised it back to the client, etc.. all useful info.

As for your 01403 error, look for SELECT INTO statements that might not find any rows, and also look for any collection/array subscripts that might not exist.