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;