Real-time dbms_output

[Rick O]

When I run an anonymous PL/SQL block in a Test window, my DBMS_OUTPUT.PUT_LINEs don't appear in the output window until execution ends (either successfully, or with a break/error). Even when I step through the code, the output doesn't appear. What am I doing wrong?

 

[Christo Kutrovsky]

Nothing, that's the way dbms_output works.

It only emulates "echo" it's very different.

During the execution of your program, the lines are stored in a buffer, and after it has finished, you have to call "read" to get them out. The client (sqlplus) for examples does that transperantly.

I am not sure wheter you can read the output during a debug of a pl/sql procedure.

 

[gulhaugen]

There is a fantastic feature called dbms_pipe, and then you can use event monitor to listen, or create your own tool.

I use this to give feedback to users ongoing batch jobs.

Morten

 

[ScottMattes]

Originally posted by gulhaugen:
There is a fantastic feature called dbms_pipe, and then you can use event monitor to listen, or create your own tool.

I use this to give feedback to users ongoing batch jobs.

Morten

Would you please give an example of using this method please? This would/could be of great use for debugging!

Thank you!

 

[patch]

IMHO for debugging you should use a debug table which is filled using autonomous transactions. That way your log isn't removed when you close the session.

kindest regards,
Patrick

 

[gulhaugen]

I would like to know what people use this for.

Any feedback on your experience with this highly appreciated.

My intention is to write an article in this issue, used in conjunction with asynch webservices.

You can use this in an plsql program or in select/update. To get this to work with DML it requires an SQL trick, let me know if you are interested.

Ok, here it goes:
Type 1:
create or replace type EVENT_FEEDBACK_ROWTYPE as object
( sid number,
serialid number,
message varchar2(400),
progress number,
expected_total number
)
Type 2:
CREATE OR REPLACE TYPE EVENT_FEEDBACK_TYPE as table of EVENT_FEEDBACK_ROWTYPE

Procedures:

function read_message(v_pipename varchar2) return event_feedback_type
pipelined is
sta integer := 0;

v_event_feedback_rowtype event_feedback_rowtype := event_feedback_rowtype(null,null,null,null,null);
begin
sta := dbms_pipe.receive_message(v_pipename, 0);
while sta = 0
loop
dbms_pipe.unpack_message(v_event_feedback_rowtype.sid);
dbms_pipe.unpack_message(v_event_feedback_rowtype.serialid);
dbms_pipe.unpack_message(v_event_feedback_rowtype.message);
dbms_pipe.unpack_message(v_event_feedback_rowtype.progress);
dbms_pipe.unpack_message(v_event_feedback_rowtype.expected_total);

sta := dbms_pipe.receive_message(v_pipename, 0);
pipe row(v_event_feedback_rowtype);
end loop;
return;
end;
procedure read_pipe(v_pipename varchar2, messages out sys_refcursor) is

begin
open messages for
select *
from table(cast(read_message(v_pipename) as event_feedback_type));
end read_pipe;

function write_message(v_pipename varchar2, v_msg varchar2, v_progress number, v_expected_total number) return number is
dummy integer;
v_sid number;
v_serial number;
begin
select sid, serial#
into v_sid, v_serial
from v$session
where audsid = sys_context('userenv', 'sessionid');

dbms_pipe.pack_message(v_sid);
dbms_pipe.pack_message(v_serial);
dbms_pipe.pack_message(v_msg);
dbms_pipe.pack_message(v_progress);
dbms_pipe.pack_message(v_expected_total);

dummy := dbms_pipe.send_message(v_pipename);
return 1;
end;