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;