Edit D:\app\Administrator\product\11.2.0\dbhome

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

REM $Header: wfjmsqc2.sql 26.4 2005/01/06 07:10:08 dmani noship $
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single &phase=tbm+1 \
REM dbdrv: checkfile:~PROD:~PATH:~FILE \
REM dbdrv: &un_fnd &pw_fnd
REM ***********************************************************************
REM   NAME
REM       wfjmsqc2.sql - 
REM   DESCRIPTION
REM       Creates all Advanced Queues with JMS Text structures.
REM       NOTE: storage clause REMoved so as to use defaults.
REM	  NOTE: sort list changed to priority, enq_time
REM   USAGE
REM       sqlplus <APPSusr>/<APPSpwd> @wfjmsqc.sql <FNDusr> <FNDpwd>
REM       sqlplus <WFusr>/<WFpwd> @wfjmsqc2.sql <WFusr> <WFpwd>
REM   MODIFICATION LOG:
REM       Created to replace wfjmsqc.sql for backward compatibility reasons.
REM ********************************************************************/

SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

REM Queues require storage and consequently must be run in the base account
REM (autopatch will run all scripts in apps account)
connect &1/&2;

REM Continue in case of error where constraints aready exist
REM WHENEVER SQLERROR CONTINUE;

REM ============================================================
REM
REM Create New Queue Table
REM
REM============================================================

declare
  queue_table_exists exception;
  pragma EXCEPTION_INIT(queue_table_exists, -24001);
begin

--  dbms_output.put_line('===================================');
--  dbms_output.put_line('Creating all Workflow JMS Topic ');
--  dbms_output.put_line('===================================');
 
  begin 
   dbms_aqadm.create_queue_table
     (
	queue_table          => 'WF_JMS_IN',
	queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
	sort_list	     => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
	comment	             => 'Workflow JMS Topic',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr1= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
  end;
  begin
   dbms_aqadm.create_queue_table
     (
        queue_table          => 'WF_JMS_OUT',
        queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        sort_list            => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
        comment              => 'Workflow JMS Topic',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr2= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin
   dbms_aqadm.create_queue_table
     (
        queue_table          => 'WF_CONTROL',
        queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        sort_list            => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
        comment              => 'Workflow JMS Topic',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr3= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
  end;
  begin
   dbms_aqadm.create_queue_table
     (
        queue_table          => 'WF_NOTIFICATION_IN',
        queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        sort_list            => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
        comment              => 'Workflow JMS Topic',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr2= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
  begin
   dbms_aqadm.create_queue_table
     (
        queue_table          => 'WF_NOTIFICATION_OUT',
        queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        sort_list            => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
        comment              => 'Workflow JMS Topic',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr2= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin
   dbms_aqadm.create_queue_table
     (
        queue_table          => 'WF_JAVA_DEFERRED',
        queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        sort_list            => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
        comment              => 'Workflow Java Deferred Queue Table',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr2= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin
   dbms_aqadm.create_queue_table
     (
        queue_table          => 'WF_JAVA_ERROR',
        queue_payload_type   => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        sort_list            => 'PRIORITY,ENQ_TIME',
        multiple_consumers   => TRUE,
        comment              => 'Workflow Java Error Queue Table',
        compatible           => '8.1'
     );

exception
    when queue_table_exists then
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr2= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
end;
/

REM====================================================================
REM
REM Create New Topic
REM
REM====================================================================

declare
  queue_exists exception;
  pragma EXCEPTION_INIT(queue_exists, -24006);

begin
--  dbms_output.put_line('=================================');
--  dbms_output.put_line('Creating all Workflow JMS Topics');
--  dbms_output.put_line('=================================');

begin
  dbms_aqadm.create_queue
	(
	  queue_name		=> 'WF_JMS_IN',
	  queue_table		=> 'WF_JMS_IN',
	  comment		=> 'Workflow JMS Topics'
	);

exception
    when queue_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr4= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin 
  dbms_aqadm.create_queue
	(
	  queue_name		=> 'WF_JMS_OUT',
	  queue_table		=> 'WF_JMS_OUT',
	  comment		=> 'Workflow JMS Topics'
	);

exception
    when queue_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr5= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin 
  dbms_aqadm.create_queue
	(
	  queue_name		=> 'WF_CONTROL',
	  queue_table		=> 'WF_CONTROL',
	  comment		=> 'Workflow JMS Topics'
	);

exception
    when queue_exists then 
      null;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr6= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin 
    dbms_aqadm.create_queue
	(
	  queue_name		=> 'WF_NOTIFICATION_IN',
	  queue_table		=> 'WF_NOTIFICATION_IN',
          max_retries           => 5,
          retry_delay           => 3600,
          retention_time        => 86400,
	  comment		=> 'Workflow JMS Topics'
	);

exception
    when queue_exists then 
      -- alter the queue to update the values
      begin
         dbms_aqadm.alter_queue
              (
                queue_name      => 'WF_NOTIFICATION_IN',
                max_retries     => 5,
                retry_delay     => 3600,
                retention_time  => 86400
              );
      exception
         when others then
            raise_application_error(-20000, 'Oracle Error Mkr6= '
                                   ||to_char(sqlcode)||' - '||sqlerrm);
      end;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr6= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin 
    
    --
    -- NOTE: You must keep this file and admin/sql/wfntfqup.sql in sync when 
    -- modifying WF_NOTIFICATION_OUT.
    --

--
    -- NOTE: retry_delay will only be invoked in case of a rollback.  Rollbacks
    -- will only occur if we are unable to process the row due to locking
    -- (which should never happen) or patching or if some other unexpected
    -- problem occurs.  Thus, retry_delay at 1 hour with 5-10 max_retries 
    -- is good because it will give a day for it to go to the exception 
    -- queue.
    --
    dbms_aqadm.create_queue
	(
	  queue_name		=> 'WF_NOTIFICATION_OUT',
	  queue_table		=> 'WF_NOTIFICATION_OUT',
          max_retries           => 5,
          retry_delay           => 3600,
          retention_time        => 86400,
	  comment		=> 'Workflow JMS Topics'
	);

exception
    when queue_exists then 
       -- Alter the queue to update the values
       begin
         dbms_aqadm.alter_queue
              (
                queue_name      => 'WF_NOTIFICATION_OUT',
                max_retries     => 5,
                retry_delay     => 3600,
                retention_time  => 86400
              );
      exception
         when others then
            raise_application_error(-20000, 'Oracle Error Mkr6= '
                                   ||to_char(sqlcode)||' - '||sqlerrm); 
      end;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr6= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin
  dbms_aqadm.create_queue
        (
          queue_name            => 'WF_JAVA_DEFERRED',
          queue_table           => 'WF_JAVA_DEFERRED',
          max_retries           => 5,
          retry_delay           => 3600,
          retention_time        => 86400,
          comment               => 'Workflow Java Deferred Queue'
        );

exception
    when queue_exists then 
       -- Alter the queue to update the values
       begin
         dbms_aqadm.alter_queue
              (
                queue_name      => 'WF_JAVA_DEFERRED',
                max_retries     => 5,
                retry_delay     => 3600,
                retention_time  => 86400
              );
      exception
         when others then
            raise_application_error(-20000, 'Oracle Error Mkr6= '
                                   ||to_char(sqlcode)||' - '||sqlerrm); 
      end;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr6= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 begin
  dbms_aqadm.create_queue
        (
          queue_name            => 'WF_JAVA_ERROR',
          queue_table           => 'WF_JAVA_ERROR',
          max_retries           => 5,
          retry_delay           => 3600,
          retention_time        => 0,
          comment               => 'Workflow Java Error Queue'
        );

exception
    when queue_exists then 
       -- Alter the queue to update the values
       begin
         dbms_aqadm.alter_queue
              (
                queue_name      => 'WF_JAVA_ERROR',
                max_retries     => 5,
                retry_delay     => 3600,
                retention_time  => 0
              );
      exception
         when others then
            raise_application_error(-20000, 'Oracle Error Mkr6= '
                                   ||to_char(sqlcode)||' - '||sqlerrm); 
      end;
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr6= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
 end;
END;
/

REM===================================================================
REM
REM Start Topic
REM
REM===================================================================

declare
begin

--  dbms_output.put_line('============================');
--  dbms_output.put_line('Starting all Workflow Queues ');
--  dbms_output.put_line('============================');

dbms_aqadm.start_queue(queue_name => 'WF_JMS_IN');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr7= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/
begin
  dbms_aqadm.start_queue(queue_name => 'WF_JMS_OUT');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr8= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/
begin 
  dbms_aqadm.start_queue(queue_name => 'WF_CONTROL');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr9= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

begin 
  dbms_aqadm.start_queue(queue_name => 'AQ$_WF_CONTROL_E',
                         enqueue    => FALSE);

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr9= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

begin 
  dbms_aqadm.start_queue(queue_name => 'WF_NOTIFICATION_IN');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr9= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/
begin 
  dbms_aqadm.start_queue(queue_name => 'WF_NOTIFICATION_OUT');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr9= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/
begin
  dbms_aqadm.start_queue(queue_name => 'WF_JAVA_DEFERRED');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr9= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/
begin
  dbms_aqadm.start_queue(queue_name => 'WF_JAVA_ERROR');

exception
    when others then
        raise_application_error(-20000, 'Oracle Error Mkr9= '
                                ||to_char(sqlcode)||' - '||sqlerrm);
end;
/

commit;
exit;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de