0

I have a stored procedure and have put some debug messages like 

call dbms_output.put ('calling clean up on <tableName>');

while invoking the procedure, I don't see this line. I am using Aqua data studio for my work. I also did the following while calling the SP

set serveroutput on@ and set serveroutput on

but getting the error like

An unexpected token "on@" was found following "set serveroutput ".  Expected tokens may include:  "="

Can you please suggest as to how to see the statements which I am putting as kind of logs within a SP? I am using "AquaData Studio - 7.0.39"

mustaccio
  • 18,234
  • 16
  • 48
  • 57
user1588737
  • 280
  • 2
  • 6
  • 12
  • http://www.aquafold.com/aquadatastudio/db2_sql_debugger.html ---- http://publib.boulder.ibm.com/infocenter/idm/v2r1/index.jsp?topic=/com.ibm.debug.spd.doc/topics/cbsovrv.html – Peter Schuetze Nov 19 '13 at 19:49

1 Answers1

0

set serveroutput is not an SQL statement so it makes no sense to Aqua Data Studio or any other tool, except the IBM DB2 command line processors.

What dbms_output.put does is store the line in an internal array in the dbms_output module. DB2 tools access that array and print its contents to screen after executing your stored procedure.

You can in a way simulate the same behaviour in Aqua Data Studio by creating an exception handler in your stored procedure where you use dbms_output.get_lines to return the line array, then build a cursor from that array using unnest() and return the cursor to the calling application (Aqua).

It might be easier though to just create a logging stored procedure of your own, running an autonomous transaction, that would insert log records into a database table that you can query when necessary.

mustaccio
  • 18,234
  • 16
  • 48
  • 57