Using DBMS_PROFILES i don't think its possible to know the values of variables. It just tells the execution time of each block ( loops, cursors, inserts etc ) of code in the procedure.
Im not sure of the debugger in TOAD, but i have used the pl/sql debugger in PL/SQL Developer tool. Here you can see the propagation of the code, values of variables, OUT variable's and cursor's values/data set etc. I think this debugger would help.
Thanks for responding Michael.
Is there any way I can track both the program execution and process flow, and at the same time capture (or logging) all the variable values?
Will DBMS_TRACE help me in that case?
RanitB - I have not use DBMS_TRACE package. As mentioned earlier PL/SQL Developer tool helps in debugging. You can see the program/procedure flow/propagation and the local variable's values using this debugger. First you need to get the system privilege DEBUG ANY PROCEDURE and DEBUG CONNECT SESSION to debug. After doing this right-click on the procedure's name you want to debug and select Test. A new test window appears, providing the necessary input parameters to the procedure click on F9 or the icon present above the BEGIN keyword to start debug. The test window will be loaded with entire procedure you wanted to test. Either you can click on the Run button (identified by the Play icon) in green color - this executes the proc step by step on its own or press the icon present next to Run button (shortcut Ctrl+N) every time to go from one step to next manually in the procedure. In the bottom pane there are 3 columns: Variable, Value and Call stack. If you copy paste the local variables into this Variable column, the value gets automatically populated when the corresponding variables get assigned some value during the course of execution.
Message was edited by: michaelrozar17 - missed one of the sys privs
If your objective is to reverse engineer a existing process then DBMS_PROFILER or DBMS_TRACE will not be of much use. I have been there. I have always found using pencil and paper and some quality time with the program helps in reverse engineering. There are some fancy tools out there but cant beat your pencil and paper approach
There are certain aspect of software engineering which are best done by manual approach, I feel till date reverse engineering is one among them!!