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?
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.
How to force a flush of dbms_output.put_line, Database PL/SQL Packages and Types Reference In this way, you can design your application so that subprograms are available only when a client is able to There is no mechanism to flush output during the execution of a procedure. A predefined exception is an internally defined exception that PL/SQL has given a name. For example, ORA-06500 (PL/SQL: storage error) has the predefined name STORAGE_ERROR. For more information, see "Predefined Exceptions". User-defined. You can declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or
If it is possible to you, you should replace the calls to dbms_output.put_line by your own function.
Here is the code for this function
WRITE_LOG -- if you want to have the ability to choose between 2 logging solutions:
write logs to a table in an autonomous transaction
CREATE OR REPLACE PROCEDURE to_dbg_table(p_log varchar2) -- table mode: -- requires -- CREATE TABLE dbg (u varchar2(200) --- username -- , d timestamp --- date -- , l varchar2(4000) --- log -- ); AS pragma autonomous_transaction; BEGIN insert into dbg(u, d, l) values (user, sysdate, p_log); commit; END to_dbg_table; /
or write directly to the DB server that hosts your database
This uses the Oracle directory
CREATE OR REPLACE PROCEDURE to_dbg_file(p_fname varchar2, p_log varchar2) -- file mode: -- requires --- CREATE OR REPLACE DIRECTORY TMP_DIR as '/directory/where/oracle/can/write/on/DB_server/'; AS l_file utl_file.file_type; BEGIN l_file := utl_file.fopen('TMP_DIR', p_fname, 'A'); utl_file.put_line(l_file, p_log); utl_file.fflush(l_file); utl_file.fclose(l_file); END to_dbg_file; /
WRITE_LOG procedure which can switch between the 2 uses, or be deactivated to avoid performances loss (
CREATE OR REPLACE PROCEDURE write_log(p_log varchar2) AS -- g_DEBUG can be set as a package variable defaulted to FALSE -- then change it when debugging is required g_DEBUG boolean := true; -- the log file name can be set with several methods... g_logfname varchar2(32767) := 'my_output.log'; -- choose between 2 logging solutions: -- file mode: g_TYPE varchar2(7):= 'file'; -- table mode: --g_TYPE varchar2(7):= 'table'; ----------------------------------------------------------------- BEGIN if g_DEBUG then if g_TYPE='file' then to_dbg_file(g_logfname, p_log); elsif g_TYPE='table' then to_dbg_table(p_log); end if; end if; END write_log; /
And here is how to test the above:
1) Launch this (file mode) from your SQLPLUS:
BEGIN write_log('this is a test'); for i in 1..100 loop DBMS_LOCK.sleep(1); write_log('iter=' || i); end loop; write_log('test complete'); END; /
2) on the database server, open a shell and
tail -f -n500 /directory/where/oracle/can/write/on/DB_server/my_output.log
dbms_output, UTL_FILE, which allows a PL/SQL program to read information from a disk file, and write information to a file. Then C-compile and link in the normal way. #include There is no mechanism to flush output during the execution of a procedure. SQL> alter system flush buffer_cache; SQL> -- original version select case a.status when 'A' then 'ACTIVE' else 'INACTIVE' end, b.* from a, b where a.id = b.id and a.status in ('A', 'I'); SQL> alter system flush buffer_cache; SQL> --new version select 'ACTIVE', b.* from a, b where a.id = b.id and a.status = 'A' union all select 'INACTIVE', b.* from a, b where a.id = b.id and a.status = 'I' SQL> --compare elapsed times.
You can insert your logging details in a logging table by using an autonomous transaction. You can query this logging table in another SQLPLUS/Toad/sql developer etc... session. You have to use an autonomous transaction to make it possible to commit your logging without interfering the transaction handling in your main sql script.
Another alternative is to use a pipelined function that returns your logging information. See here for an example: http://berxblog.blogspot.com/2009/01/pipelined-function-vs-dbmsoutput.html When you use a pipelined function you don't have to use another SQLPLUS/Toad/sql developer etc... session.
PL/SQL Input/Output, The built-in packages offer a number of ways to generate output from within your While updating a database table is, of course, a form of "output" from PL/SQL, The DBMS_OUTPUT package is created when the Oracle database is Disables output from the package; the DBMS_OUTPUT buffer will not be flushed to the For example, SQL*Plus does not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program. SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE ('hello'); 3 DBMS_LOCK.SLEEP (10); 4 END;
the buffer of
DBMS_OUTPUT is read when the procedure
DBMS_OUTPUT.get_line is called. If your client application is SQL*Plus, it means it will only get flushed once the procedure finishes.
You can apply the method described in this SO to write the
DBMS_OUTPUT buffer to a file.
[Chapter 6] Generating Output from PL/SQL Programs, 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 See below ways to clear the screen. SQL> clear screen which can also be shortened to. SQL> cl scr And you can also use: SQL> host clear Or. SQL> ho clear For your toad , in the DBMS Output tab there is first button right to watch, is used to clear the output of buffer.
Set session metadata MODULE and/or ACTION using
Monitor with OEM, for example:
Module: ArchiveData Action: xxx of xxxx
Is there any way to flush output from PL/SQL in Oracle? -, I have a SQL script that is called from within the shell script and it takes a long time to run. Currently it contains the dbms_output.put_line 5 dbms_output.put_line (v_line); 6 end; 7 / Hello World PL/SQL procedure successfully completed. The first time the script is run, the result was just a notice that the script completed successfully. Once we set serverouput on and rerun the script, the results are shown. As discussed earlier, this is an anonymous block of PL/SQL code.
Oracle PL/SQL Programming: Covers Versions Through Oracle Database , Covers Versions Through Oracle Database 11g Release 2 Steven Feuerstein, Bill This only occurs after the outermost PL/SQL block terminates; you cannot use The way to write information to this buffer is by calling the DBMS_OUTPUT. SET SERVEROUTPUT ON SIZE UNLIMITED In addition to enabling output to the 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
Oracle PL/SQL Programming: A Developer's Workbook: Oracle , What is the maximum size of the buffer for any single PL/SQL block execution? How can you flush the DBMS_OUTPUT buffer and turn off output from this As part my project I had run a PL/SQL block to process more than 13,00,000 records from 12 different tables. To know the execution flow I have already included SET SERVEROUTPUT ON command. My requirement is to know the execution flow while the block is still running and the SET SERVEROUTPUT ON only displays the flow after PL/SQL block execution.
Oracle dbms_output tips, The PL/SQL dbms_output package has a put_line procedure to allow you to When using dbms_output inside SQL*Plus, you need to use the "set serveroutput on The dbms_output package provides an excellent debugging tool when used I'm running 188.8.131.52 on Linux (RHAS 3). I've got a PL/SQL code base that runs fine under Windows 2000. It's mostly batch stuff. However, on this new Linux installation, I'm getting buffer overflows (ORA-20000, etc.) from DBMS_OUTPUT when the code is run AS A JOB (total background process).