view values of variables from oracle PL/SQL

Question

I'm running a PL/SQL block that is supposed to be calling a stored procedure who's output parameters are supposed to be populating variables in the PL/SQL block.

The procedure compiles, and the PL/SQL block runs successfully. But I'd like to check the values of the variables populated by the procedure. Is there a way to output these values?

I'm using Free TOAD if that helps.

Thanks,

Answer 1

dbms_output.put_line(varHere);

Answer 2

You have a few options:

• Log with DBMS_OUTPUT
• Log to a file using UTL_FILE
• Use the Oracle debugger DBMS_DEBUG

My preference is to log to a file using a fairly simple custom logging package. In my custom logging package I have a configurable flag to switch between file logging and DBMS_OUTPUT logging.

The Oracle debugging API's are quite good but you definitely need a good debugging client. Oracle SQL Developer has fairly good support for it.

There is also a log4plsql logger but I haven't used it.

Answer 3

A fourth option (the one I usually default to) also exists.....logging debugging messages to a database table. This gives you the advantage of being able to sort, query, filter your debug messages as required.

As darreljnz mentioned above, log4plsql also probably supports logging to the database. I find that 1) dbms_output has too many limitations 2) logging to a file requires too much setup, and a database restart if the utl_file_dir parameter is not already set 3) using dbms_debug without the help of a GUI is very cumbersome (but I've done it)

Answer 4

If you can download a copy of Oracle jDeveloper jDeveloper Home page or Oracle Visual Studio add in VS Plugin. Both are free, and allow you to debug (set breakpoint, inspect variables, ...) in PL/SQL on the db. You also need a "debug" privledge on the DB to do this. But, if you have dba rights you can grant that.