Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wfq_dl.sql
REM $Header: wfq_dl.sql 26.0 2004/12/08 02:07:26 kma noship $ REM /*======================================================================+ REM | Copyright @ 2004, Oracle. All rights reserved. REM +======================================================================+ REM | FILENAME REM | wfq_dl.sql REM | DESCRIPTION REM | Restarts Workflow Deferred, Inbound, and Outbound queue as read REM | only. Populate WF_QUEUE_TMP table base on the data in Inbound REM | and Outbound queue. REM | USAGE REM | sqlplus wfusr/wfpwd @wfq_dl.sql wfusr wfpwd REM *======================================================================*/ set verify off set arraysize 1 WHENEVER SQLERROR EXIT FAILURE; connect &1/&2 set serveroutput on size 100000; WHENEVER SQLERROR EXIT; declare cnt pls_integer; begin select count(1) into cnt from user_objects where object_name = 'WF_PAYLOAD_T'; if (cnt <> 0) then raise_application_error(-20000, 'Queues have been migrated.'); end if; select count(1) into cnt from user_objects where object_name = 'WF_QUEUE_TMP'; if (cnt <> 0) then raise_application_error(-20000, 'Queues have been dequeued.'); end if; end; / WHENEVER SQLERROR CONTINUE; -- create temp tables create table wf_queue_tmp ( queue_name varchar2(30) not null, msgid raw(16), itemtype varchar2(8), itemkey varchar2(240), actid number, function_name varchar2(200), param_list varchar2(4000), result varchar2(30) ); / declare deq_options dbms_aq.dequeue_options_t; mesg_prop dbms_aq.message_properties_t; mesg_handle raw(16); mesg_payload system.wf_payload_t; dequeue_timeout exception; pragma EXCEPTION_INIT(dequeue_timeout, -25228); ctr pls_integer := 0; begin -- set dequeue option deq_options.dequeue_mode := dbms_aq.REMOVE; deq_options.wait := dbms_aq.NO_WAIT; deq_options.navigation := dbms_aq.FIRST_MESSAGE; -- restart queue as read only dbms_aqadm.stop_queue(queue_name=> 'WF_INBOUND_QUEUE'); dbms_aqadm.start_queue ( queue_name=> 'WF_INBOUND_QUEUE', enqueue=>FALSE, dequeue=>TRUE ); dbms_output.put_line('Start migrating WF_INBOUND_QUEUE'); -- dequeue and populate temp table; cannot do dequeue / -- enqueue directly since queue table name has to be unique. <<queue_loop>> loop begin dbms_aq.dequeue ( queue_name => 'WF_INBOUND_QUEUE', dequeue_options => deq_options, message_properties => mesg_prop, payload => mesg_payload, msgid => mesg_handle ); exception when dequeue_timeout then exit queue_loop; -- should not happen, but just in case. when others then raise_application_error(-20000, 'Oracle Error Rebuild1= ' ||to_char(sqlcode)||' - '||sqlerrm); end; dbms_output.put_line('kma dbg: inserting ['||mesg_handle||']'); insert into wf_queue_tmp (queue_name, msgid, itemtype, itemkey, actid, function_name, param_list, result) values ('WF_INBOUND_QUEUE', mesg_handle , mesg_payload.itemtype, mesg_payload.itemkey, mesg_payload.actid, mesg_payload.function_name, mesg_payload.param_list, mesg_payload.result); commit; ctr := ctr + 1; deq_options.navigation := dbms_aq.NEXT_MESSAGE; end loop queue_loop; dbms_output.put_line('WF_INBOUND_QUEUE dequeue completed, '||to_char(ctr)||' messages found.'); end; / declare deq_options dbms_aq.dequeue_options_t; mesg_prop dbms_aq.message_properties_t; mesg_handle raw(16); mesg_payload system.wf_payload_t; dequeue_timeout exception; pragma EXCEPTION_INIT(dequeue_timeout, -25228); ctr pls_integer := 0; begin -- set dequeue option deq_options.dequeue_mode := dbms_aq.REMOVE_NODATA; deq_options.wait := dbms_aq.NO_WAIT; deq_options.navigation := dbms_aq.FIRST_MESSAGE; -- restart queue as read only dbms_aqadm.stop_queue(queue_name=> 'WF_OUTBOUND_QUEUE'); dbms_aqadm.start_queue ( queue_name=> 'WF_OUTBOUND_QUEUE', enqueue=>FALSE, dequeue=>TRUE ); dbms_output.put_line('Start migrating WF_OUTBOUND_QUEUE'); -- dequeue and populate temp table; cannot do dequeue / -- enqueue directly since queue table name has to be unique. <<queue_loop>> loop begin dbms_aq.dequeue ( queue_name => 'WF_OUTBOUND_QUEUE', dequeue_options => deq_options, message_properties => mesg_prop, payload => mesg_payload, msgid => mesg_handle ); exception when dequeue_timeout then exit queue_loop; -- should not happen, but just in case. when others then raise_application_error(-20000, 'Oracle Error Rebuild2= ' ||to_char(sqlcode)||' - '||sqlerrm); end; dbms_output.put_line('kma dbg: inserting ['||mesg_handle||']'); insert into wf_queue_tmp (queue_name, msgid, itemtype, itemkey, actid, function_name, param_list, result) values ('WF_OUTBOUND_QUEUE', mesg_handle , mesg_payload.itemtype, mesg_payload.itemkey, mesg_payload.actid, mesg_payload.function_name, mesg_payload.param_list, mesg_payload.result); commit; ctr := ctr + 1; deq_options.navigation := dbms_aq.NEXT_MESSAGE; end loop queue_loop; dbms_output.put_line('WF_OUTBOUND_QUEUE dequeue completed, '||to_char(ctr)||' messages found.'); end; / declare def_q_name varchar2(50); begin select name into def_q_name from dba_queues where owner = '&&1' and name like 'WF_DEFERRED_QUEUE' or name like 'WF_DEFERRED_QUEUE_M'; dbms_output.put_line('Restarting '||def_q_name); -- restart queue as read only dbms_aqadm.stop_queue(queue_name=> def_q_name); dbms_aqadm.start_queue ( queue_name=> def_q_name, enqueue=>FALSE, dequeue=>TRUE ); exception when others then dbms_output.put_line('Error on WF_DEFERRED_QUEUE_M '||sqlerrm); null; end; / create or replace type wf_payload_arr is varray(2000) of system.wf_payload_t; / -- We do not need to dequeue from WF_DEFERRED table since all -- info except corr id can be found in Workflow runtime tables -- We can only take care of state: -- 0: READY -- 1: WAIT -- Do nothing for messages that have been processed or -- that are in the exception queue. exit;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de