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 🙂