Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\admin\sql\wfaqback.sql

REM dbdrv: none
REM $Header: wfaqback.sql 26.0 2005/02/28 11:24:34 dmani noship $
REM +=========================================================================+
REM |            Copyright (c) 2005, Oracle. All rights reserved.             |
REM +=========================================================================+
REM 
REM NAME
REM   wfaqback.sql - WorkFlow AQ BACK up script
REM
REM DESCRIPTION
REM   This script helps to back up a given Workflow Advanced Queue(of WF_EVENT_T
REM   payload) for a list of given event names. The event messages are stored in
REM   an intermediate table. Once the queue in question is recreated, the events
REM   can be re-enqueued on to the queue using wfaqrenq.sql which will take the 
REM   messages from the intermediate table and re-equeue them.
REM
REM   See Method 2 in the update *** SACSHARM  01/19/05 06:32 pm ***
REM   of Bug 4111510 for the steps to drop and recreate a queue.
REM
REM USAGE
REM   sqlplus apps/<apps pwd> @wfaqback.sql <Agent Name>
REM
REM NOTE
REM   o Please note that the user needs to specify the list of event messages that
REM     are *NOT* to be backed up in list *l_unwanted_events* below. These
REM     messages will not be backed up for re-enqueueing later using wfaqrenq.sql.
REM
REM   o If % is specified in the list, it means that all the events are to be
REM     removed from the queue and none is required to be backed up. This is same
REM     as recreating the queue without backing up events.
REM +=========================================================================+

set verify off;
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;

REM
REM Create a table to backup the messages. CREATE GLOBAL TEMPORARY TABLE
REM does not work since we have wf_event_t datatype in the table
REM

declare
  l_agent_name   varchar2(128) := upper('&&1');
  l_sql_str      varchar2(4000);

object_exists EXCEPTION;
  pragma exception_init(object_exists, -955);
begin
  -- Create a temporary table to backup messages from different queues
  l_sql_str := 'CREATE TABLE wf_queue_temp_table '||
               '  (enq_time  date, '||
               '   queue     varchar2(30), '||
               '   corr_id   varchar2(128), '||
               '   delay     date, '||
               '   user_data wf_event_t)';

execute immediate l_sql_str;
exception
  when object_exists then
    l_sql_str := ' DELETE FROM wf_queue_temp_table '||
                 ' WHERE queue = '''||l_agent_name||'''';
    execute immediate l_sql_str;
  when others then
    raise_application_error(-20000, 'Oracle Error = '||
				to_char(sqlcode)||' - '||sqlerrm); 
end;
/
commit;

REM
REM Back up the events as per the users exclusion list given above
REM

declare
  l_schema       varchar2(128) := wf_core.translate('WF_SCHEMA');
  l_agent_name   varchar2(128) := upper('&&1');
  l_queue        varchar2(128) := l_schema||'.aq$'||l_agent_name;
  l_corr_id      varchar2(128);
  l_account_name varchar2(128);
  l_where        varchar2(32000);
  l_sql_str      varchar2(32000);
  l_rows         number;

TYPE t_strings_tbl is table of varchar2(80);

TYPE t_bkup_msg IS REF CURSOR;
  c_bkup_msg      t_bkup_msg;

TYPE t_enq_time_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
  TYPE t_queue_tbl  IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  TYPE t_corr_id_tbl IS TABLE OF VARCHAR2(128) INDEX BY BINARY_INTEGER;
  TYPE t_user_data_tbl IS TABLE OF WF_EVENT_T INDEX BY BINARY_INTEGER;
  TYPE t_delay_tbl  IS TABLE OF DATE INDEX BY BINARY_INTEGER;

l_enq_time_tbl  t_enq_time_tbl;
  l_queue_tbl     t_queue_tbl;
  l_corr_id_tbl   t_corr_id_tbl;
  l_delay_tbl     t_delay_tbl;
  l_user_data_tbl t_user_data_tbl;
 
  -- #### UPDATE THIS TABLE WITH EVENT NAMES THAT YOU WANT TO REMOVED  #### --
  -- #### If you specify no event or % here, all event will be removed #### --
  l_unwanted_events t_strings_tbl :=
         t_strings_tbl('oracle.apps.wf.notification%'
                        -- ,'oracle.apps.cs.sr.ServiceRequest%'
                        -- Add more event names here that you want to be REMOVED
                       );

begin
  -- Can be adjusted accordingly
  l_rows := 5000;

-- Set the correlation id
  if (l_unwanted_events is null or l_unwanted_events.count = 0) then
    l_where := ' corr_id NOT LIKE ''%''';
  else

l_account_name := null;
    -- Set the account name for WF_% agents
    if (upper(l_agent_name) like 'WF_%') then
       if (wf_event.account_name is null) then
	  wf_event.SetAccountName;
       end if;
       l_account_name := wf_event.account_name;
    end if;

l_corr_id  := l_account_name || ':' || l_unwanted_events(1);
    l_where := ' corr_id NOT LIKE ''' || l_corr_id || '''';

for i in 2..l_unwanted_events.LAST loop   
      l_corr_id  := l_account_name || ':' || l_unwanted_events(i);
      l_where := l_where || ' AND corr_id NOT LIKE ''' || l_corr_id || '''';
    end loop;
  end if;
     
  l_sql_str := 'SELECT enq_time, '||
                     ' queue, '||
                     ' corr_id, '||
                     ' delay, '||
                     ' user_data '||
              ' FROM '|| l_queue ||
              ' WHERE ' || l_where ||
              ' AND msg_state in (''READY'', ''WAITING'')'||
              ' ORDER BY ENQ_TIME';

OPEN c_bkup_msg FOR l_sql_str;

loop

-- Bulk fetch 5000 records each time
    FETCH c_bkup_msg BULK COLLECT INTO l_enq_time_tbl,
                                       l_queue_tbl,
				       l_corr_id_tbl,
				       l_delay_tbl,
                                       l_user_data_tbl
    LIMIT l_rows;

exit when l_enq_time_tbl.COUNT = 0;
    
    -- Insert into the temporary table
    forall j in l_enq_time_tbl.FIRST..l_enq_time_tbl.LAST
      INSERT INTO wf_queue_temp_table(enq_time, 
                                      queue,
                                      corr_id, 
                                      delay,
				      user_data)

VALUES (l_enq_time_tbl(j),
              l_queue_tbl(j),
              l_corr_id_tbl(j),
	      l_delay_tbl(j),
              l_user_data_tbl(j));

l_enq_time_tbl.DELETE;

commit;
  end loop;
  close c_bkup_msg;

exception
  when OTHERS then
    if (c_bkup_msg%ISOPEN) then
      close c_bkup_msg;
    end if;
    raise_application_error(-20000, 'Oracle Error = '||
			    to_char(sqlcode)||' - '||sqlerrm); 
end;
/

commit;
exit;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de