Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle PL/SQL - tips for immediate output / console printing

Tags:

I have a number of pl/sql procedures that can take several minutes to run. While developing them, I've added a few print statements to help debug and also provide some feedback and progress indicators. Initially, I ran these on small test sets and output was almost instantaneous. Now that I'm testing with larger test sets that take several minutes to run, I find that printing to the console is no longer suitable, because nothing gets printed until the procedure ends. I'm used to working in environments that do not buffer their output and print it immediately and adding simple print-statements for simple debugging and diagnostic is common.

Is it possible in pl/sql to print output immediately (not buffered)? If not, what alternatives do people recommend to get a similar result?

like image 990
FrustratedWithFormsDesigner Avatar asked Nov 13 '09 14:11

FrustratedWithFormsDesigner


People also ask

How do you display output in PL SQL?

In the DBMS Output window, choose the "plus" icon and select the connection that you want to write data to the DBMS Output window. Then run the PL/SQL block in the SQL Worksheet window using the right arrow (Ctrl+Enter in Windows). You'll see the output appear in the DBMS Output window.

Which of the following is used to print output in PL SQL?

The DBMS_OUTPUT is a built-in package that enables you to display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers.

Which command is used to direct the PL SQL output to a screen?

put_line : This command is used to direct the PL/SQL output to a screen.

Can I use DBMS_OUTPUT Put_line?

The Oracle dbms_output. put_line procedure allows you to write data to flat file or to direct your PL/SQL output to a screen. Here is a code example using dbms_output.


2 Answers

You can have a procedure that writes messages to a table using an autonomous transaction something like:

procedure log (p_message) is     pragma autonomous_transaction; begin     insert into message_log (user, datetime, message)     values (user, sysdate, p_message);     commit; end; 

Then monitor the table from another Oracle session.

like image 94
Tony Andrews Avatar answered Sep 21 '22 14:09

Tony Andrews


we have a small trick for this.

you can use DBMS_APPLICATION_INFO.set_client_info(" some information here"); creating some variables and replace the string inside " ".

and use select client_info from v$session to monitor the progress.

like image 41
Henry Gao Avatar answered Sep 23 '22 14:09

Henry Gao