Menu
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?
372
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.
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.
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.
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.
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.
147
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
