Use the following anonymous PL/SQL block (might need some re-formatting; it hasn’t pasted very well):
DECLARE
p_event_name VARCHAR2(100) :=’oracle.apps.wf.notification.summary.send’;
l_event_t wf_event_t;l_clob CLOB;
l_clob_length INTEGER:=0;
l_buffer VARCHAR2(32000);
l_chunksize BINARY_INTEGER := 32000;
l_offset INTEGER:= 1;
l_file UTL_FILE.FILE_TYPE;
l_file_name VARCHAR2(500);
l_dir dba_directories.directory_name%type;
l_dir_name dba_directories.directory_path%type;
type t_jmsq IS REF CURSOR;
c_jmsq t_jmsq;
type t_jmsq_rec IS RECORD
(msg_id RAW(16)
,msg_state VARCHAR2(13)
,consumer_name VARCHAR2(30)
,queue_name VARCHAR2(30)
,exception_queue VARCHAR2(30)
,retry_count NUMBER
,enq_time DATE
,deq_time DATE
,user_data SYS.AQ$_JMS_TEXT_MESSAGE);
l_jmsq_rec t_jmsq_rec;
BEGIN
dbms_output.enable(10000000);l_dir:=’DBA_DIR’;
SELECT directory_path
INTO l_dir_name
FROM dba_directories
WHERE directory_name = l_dir;
OPEN c_jmsq FOR
‘SELECT msg_id, msg_state, consumer_name, queue, exception_queue, ” retry_count, enq_time, deq_time, user_data” FROM applsys.aq$WF_NOTIFICATION_OUT” WHERE msg_id = ”71909BE5959CE0A4E043AC14C797E0A4”’ ‘ ORDER BY enq_time’;
LOOP
FETCH c_jmsq
INTO l_jmsq_rec;
EXIT WHEN c_jmsq%NOTFOUND;
wf_event_ojmstext_qh.deserialize(l_jmsq_rec.user_data, l_event_t); dbms_output.put_line(‘Event ‘l_event_t.event_name’ NID ‘l_event_t.event_key
‘ MSGID ‘l_jmsq_rec.msg_id);
dbms_output.put_line
(chr(9)’Enqueued ‘to_char(l_jmsq_rec.enq_time, ‘DD-MON-YYYY HH24:MI:SS’)
‘ Dequeued ‘to_char(l_jmsq_rec.deq_time, ‘DD-MON-YYYY HH24:MI:SS’)); dbms_output.put_line(chr(9)l_event_t.error_message);
dbms_output.put_line(chr(9)’CID=’l_event_t.correlation_id); l_clob:=l_event_t.event_data;
–How big is the clob?
l_clob_length := DBMS_LOB.getlength(l_clob);
–Build the output file name l_file_name:=’aidandguy.txt’;
–Open the output file
l_file := UTL_FILE.fopen(l_dir,l_file_name,’w’, 32767);
–Loop through, filling the buffer and writing to the file
WHILE l_offset < l_clob_length
LOOP
dbms_lob.read(l_clob,l_chunksize,l_offset,l_buffer); UTL_FILE.put_line(l_file, l_buffer, TRUE); l_offset:=l_offset+l_chunksize;
END LOOP;
dbms_output.put_line(l_file_name’ written to disk’);
— Close the file.
UTL_FILE.fclose(l_file);
END LOOP;
CLOSE c_jmsq;
END;