How to get dbms_output to print line before the end of the procedure?


begin
  dbms_output.put_line('Kilroy1');
  dbms_lock.sleep(1);
  dbms_output.put_line('Kilroy2');
  dbms_lock.sleep(1);
  dbms_output.put_line('Kilroy3');
  dbms_lock.sleep(1);
  dbms_output.put_line('Kilroy4');
  dbms_lock.sleep(1);
  dbms_output.put_line('Kilroy5');
  dbms_lock.sleep(1);
end;
/

You wait five seconds, then get the output.

This is the way it works with dbms_output.

Now I try a new trick

The output does not wait the end. There was a trick I wrote in 2007 using utl_file, but it was a bit cheating, because it was only working with local connections.

There are a lot of ways of simulating this, with DBMS_APPLICATION_INFO, UTL_FILE, TABLEs, DBMS_PIPE, but for today, I chosed advanced queuing.
my program has 4 parts. Here it is shell but it could well be perl / java and others


#!/bin/ksh
# 1. create the objects
./init
# 2. create a dequeuing process in the background
./monitor &
# 3. create the connection
./connect
sleep 1
# 4. cleanup the objects
./cleanup

init.sql creates a message type, a queue table and a queue. Then it creates a procedures that enqueue messages.


create type o as object(t timestamp, txt varchar2(40))
/
exec dbms_aqadm.create_queue_table('QT','O')
exec dbms_aqadm.create_queue('Q','QT')
create procedure enqueue(txt varchar2) is
  pragma autonomous_transaction;
  msgid raw(16);
  enqueue_options dbms_aq.enqueue_options_t;
  message_properties dbms_aq.message_properties_t;
begin
  dbms_aq.enqueue('Q', enqueue_options, message_properties,
    o(current_timestamp, txt), msgid);
  commit;
end;
/
exec dbms_aqadm.start_queue('Q')

In my “monitor” script, I loop my monitor.sql with a simple while : ; do sqlplus … until failure


declare 
  msg o;
  msgid raw(16);
  dequeue_options dbms_aq.dequeue_options_t;
  message_properties dbms_aq.message_properties_t;
begin
  dbms_aq.dequeue('Q', dequeue_options, message_properties, msg, msgid);
  dbms_output.put_line(msg.t||': '||msg.txt);
end;
/
quit

My code now looks like


begin
  enqueue('Kilroy1');
  dbms_lock.sleep(1);
  enqueue('Kilroy2');
  dbms_lock.sleep(1);
  enqueue('Kilroy3');
  dbms_lock.sleep(1);
  enqueue('Kilroy4');
  dbms_lock.sleep(1);
  enqueue('Kilroy5');
  dbms_lock.sleep(1);
end;
/

But thanks for my second processes, the monitor, I have now feedback before the end.

It is my first videos on this blog 🙂

1 Comment

  • I use pragma autonomous_transaction and write message to log table to see what’s happening while procedure is running.

Leave a Reply

Your email address will not be published.

*