CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
user2671057
user2671057

Reputation: 1543

How to print sqlplus output immediate

I have very simple pl\sql code.

In this code I'm printing the index of the loop and wait 1 second before each print.

My problem is that I want this output to be used like a live log, when the dbms_output.put_line procedure is invoked and print - I want to see the output immediately.

In the current code - only after it finished (5 seconds..), it prints all the output in one shot...

set serveroutput on
set echo on

begin

for i in 1..5
loop
dba_maint.pkg_utils.sp_sleep(1);
dbms_output.put_line(i);
end loop;

end;
/

Upvotes: 0

Views: 1241

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

No way, you can't. It is displayed when PL/SQL procedure has finished.

If you want to create a live log,

  • create a table
  • a sequence
  • an autonomous transaction procedure which would
    • insert a row into that table
    • using a sequence (so that you'd know how to order rows)
    • possibly a timestamp (so that you'd know how long certain step took)
    • commit (which won't affect main transaction as - remember - procedure is an autonomous transaction one)

Then put calls to the logging procedure into your long-time-run PL/SQL procedure, run it, and let it work. In another session, query the log table to view progress.

Upvotes: 2

Related Questions