How to print in oracle stored procedure (for debugging purposes)?

Question

I am trying to debug a stored procedure (Oracle). I want to print some variables.

I do not know which command to print (or where to find it). Can anyone tell me what it is?

thanks

EDIT:

This is my trigger:

create or replace procedure bns_saa_confs_update_state ( theID in varchar2 ) AS begin UPDATE BNS_SAA_CONFIRMATIONS SET SentToWssStatus='T' WHERE ID=theID; commit; end; 

I want to print an identifier

Answer 1

Use the dbms_output.put_line() function:

 declare my_var varchar2(20); begin my_var := 'Hello World'; dbms_output.put_line(my_var); end; / 

Make sure you have set serveroutput on if running from SQLPlus, or set the output if running from the IDE. Some developers will create a wrapper function to simplify debugging.

Answer 2

You probably need the DBMS_OUTPUT package, i.e.

 DECLARE a INTEGER := 0; BEGIN dbms_output.put_line( 'Starting value: ' || a ); a := a + 1; dbms_output.put_line( 'Ending value: ' || a ); END; 

Note that you usually need to enable DBMS_OUTPUT in the client application before the data is displayed. In SQL * Plus you need

 set serveroutput on; 

before executing the stored procedure so that data is displayed after execution. Other GUI tools have different approaches for including DBMS_OUTPUT .