Is it possible to get a string containing a list of the local variables names and their values at any point in time to aid in debugging (i.e. add to a row in the log table of the database as a text string)?
Asked
Active
Viewed 938 times
8
-
2Certainly it is somehow possible because there is debuging mode in Oracle DB, but I suggest you to use some IDE like PL/SQL Developer or Oracle SQL developer which have built in debugging window which allows you to do exactly this - to set break point, to step in, to see current values of variables, etc. – Martina Nov 22 '15 at 22:58
-
Personally I don't use debuggers but logging. In PL/SQL scope that's a dedicated log table, small amount of supporting PL/SQL code, conditional compilation and a lot of nicely formatted ["print"-statements](http://stackoverflow.com/q/189562/272735). – user272735 Nov 23 '15 at 08:12
-
Is there a generic way to grab all local variables to log them – Ed Heal Nov 23 '15 at 08:17
-
1Have you looked at the DBMS_DEBUG PL/SQL Package? Could possibly help, but too involved to discuss here. See the PL/SQL Packages and Types Reference (assuming 11g) – DBug Nov 24 '15 at 21:14
-
Do you have example of your thoughts? – Muhammad Muazzam Nov 26 '15 at 07:22
2 Answers
2
Is not possible to obtain a String with debug information, however using Sql Developer (Oracle free tool) you can DEBUG your pl/sql as usual likewise any other debug tool for another language including the VALUE for any variable.
This is taken from its documentation:
HTH
Osy
- 1,613
- 5
- 21
- 35
1
No, it is not possible.
DBMS_DEBUG can inspect PL/SQL variables, but it requires that the running session suspend and that a second session attach to it to perform the inspection.
I've thought about creating an API to spawn a second session (via DBMS_SCHEDULER) which would stop the calling session, inspect it, restart it, and report back.
That'd be very involved and I'm not sure it'd be a supported use case of DBMS_DEBUG.
Short of that, there is nothing.
Matthew McPeak
- 17,705
- 2
- 27
- 59