Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there any way to flush output from PL/SQL in Oracle?

I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_line statements at various points. The output from these print statements appear in the log files, but only once the script has completed.

Is there any way to ensure that the output appears in the log file as the script is running?

like image 372
baxter Avatar asked Sep 24 '09 15:09

baxter


People also ask

How can I see the output of a PL SQL block?

To do this we use a procedure called dbms_output. put_line to place the results in a buffer that SQL*Plus will retrieve and display. SQL*Plus must be told to retrieve data from this buffer in order to display the results. The SQL*Plus command 'set serveroutput on' causes SQL*Plus to retrieve and display the buffer.

How do you spool output in Oracle PL SQL?

In order to execute a spool file in plsql Go to File->New->command window -> paste your code-> execute. Got to the directory and u will find the file.

What is DBMS_OUTPUT Put_line in PL SQL?

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.

What is out mode in Plsql?

IN OUT mode:Passes an initial value to the subprogram and returns an updated value to the invoker. Formal parameter acts like an initialized variable: When the subprogram begins, its value is that of its actual parameter. Oracle recommends that the subprogram update its value.


1 Answers

Not really. The way DBMS_OUTPUT works is this: Your PL/SQL block executes on the database server with no interaction with the client. So when you call PUT_LINE, it is just putting that text into a buffer in memory on the server. When your PL/SQL block completes, control is returned to the client (I'm assuming SQLPlus in this case); at that point the client gets the text out of the buffer by calling GET_LINE, and displays it.

So the only way you can make the output appear in the log file more frequently is to break up a large PL/SQL block into multiple smaller blocks, so control is returned to the client more often. This may not be practical depending on what your code is doing.

Other alternatives are to use UTL_FILE to write to a text file, which can be flushed whenever you like, or use an autonomous-transaction procedure to insert debug statements into a database table and commit after each one.

like image 147
Dave Costa Avatar answered Sep 20 '22 11:09

Dave Costa