REM dbdrv: none
REM $Header: wfq_crtbl.sql 26.0 2004/12/08 02:18:44 kma noship $
REM /*======================================================================+
REM  | Copyright @ 2004, Oracle.  All rights reserved.
REM  +======================================================================+
REM  | FILENAME
REM  |   wfq_crtbl.sql
REM  | DESCRIPTION
REM  |   Drops SYS.WF_PAYLOAD_T based Workflow Deferred, Inbound, and 
REM  |   Outbound queues.  Creates OWF_MGR.WF_PAYLOAD_T type.
REM  | USAGE
REM  |   sqlplus wfusr/wfpwd @wfq_crtbl.sql wfusr wfpwd systempwd
REM  *======================================================================*/

set verify off
set arraysize 1


WHENEVER SQLERROR EXIT FAILURE;
connect &1/&2;
WHENEVER SQLERROR CONTINUE;

-- Dropping WF Deferred queue
declare
  def_q_tbl   varchar2(50);
  def_q_name  varchar2(50);
begin
  select queue_table into def_q_tbl 
    from dba_queue_tables
   where owner = '&&1'
     and queue_table = 'WF_DEFERRED_TABLE'
      or queue_table = 'WF_DEFERRED_TABLE_M';

  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('queue name '||def_q_name||' queue table '||def_q_tbl);  
  
  -- drop queue and queue table
  -- either WF_DEFERRED_QUEUE_M or WF_DEFERRED_QUEUE
  dbms_aqadm.stop_queue(queue_name => def_q_name);
  dbms_aqadm.drop_queue(queue_name => def_q_name);
  dbms_aqadm.drop_queue_table(queue_table => def_q_tbl);
exception
  when others then
    null;  -- ignore any error
end;
/

-- Dropping WF Inbound queue
begin
  dbms_aqadm.stop_queue(queue_name => 'WF_INBOUND_QUEUE');
  dbms_aqadm.drop_queue(queue_name => 'WF_INBOUND_QUEUE');
  dbms_aqadm.drop_queue_table(queue_table => 'WF_INBOUND_TABLE');
exception
  when others then
    null;  -- ignore any error
end;
/

-- Dropping WF Outbound queue
begin
  dbms_aqadm.stop_queue(queue_name => 'WF_OUTBOUND_QUEUE');
  dbms_aqadm.drop_queue(queue_name => 'WF_OUTBOUND_QUEUE');
  dbms_aqadm.drop_queue_table(queue_table => 'WF_OUTBOUND_TABLE');
exception
  when others then
    null;  -- ignore any error
end;
/

drop type wf_payload_arr;
/

connect system/&3;

WHENEVER SQLERROR CONTINUE;

drop type wf_payload_t;

WHENEVER SQLERROR EXIT FAILURE;

connect &1/&2;

create or replace type wf_payload_t as object (
  itemtype      varchar2(8),
  itemkey       varchar2(240),
  actid         number,
  function_name varchar2(200),
  param_list    varchar2(4000),
  result        varchar2(30)
);
/

create or replace type wf_payload_arr as varray(2000) of wf_payload_t;
/

commit;
exit;