How to Flush Output from Pl/SQL in Oracle

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

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.

Oracle SQLPLUS: immediately flush SPOOL output

You can't (as far as I can tell).

Data is spooled in chunks of 8K (typically, as Ask Tom says) so, until you fill the buffer (or issue spool off), you won't see anything.

Pl sql procedure dbms output

As already said you'll have to include a SET SERVEROUTPUT ON; statement before your DECLARE but you'll also need to handle the output on the OS level:

sqlplus -s user/pword@db '@script.sql;' > log.txt

Hint: the -s flag will hide the sqlplus version info from your log file.

Printing the value of a variable in SQL Developer

You need to turn on dbms_output.
In Oracle SQL Developer:

1. Show the DBMS Output window (View->DBMS Output).
2. Press the "+" button at the top of the Dbms Output window and then select an open database connection in the dialog that opens.

In SQL*Plus:

 SET SERVEROUTPUT ON

dbms_output.put is not printing data

Try this: Just add dbms_output.put_line('') or dbms_output.new_line at the end of the loop.

declare
v_name varchar(30);
v_len number;
v_number number;
  BEGIN

    v_name := :name;
    v_number := length(v_name);
    while v_number > 0
    loop
      dbms_output.put(v_number||'::'||substr(v_name,v_number,1)|| ' ' );
      v_number := v_number - 1;
    END loop;

    dbms_output.new_line;
  end;

P.S. Please refrain from using '&' for substitution variable (&name) in pl/sql since its a feature supported by SQL*Plus. Instead, use bind variable like this :name

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

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.

---

Related Topics