4 Replies Latest reply on Feb 13, 2020 2:02 PM by hal9000akilroy

    ORA-38818: illegal reference to editioned object APPLSYS.AQ$_WF_NOTIFICATION_OUT_V

    hal9000akilroy

      Hi

       

      after cloning from production we got error in workflow

      Error Message [WF_ERROR] ERROR_MESSAGE=ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT n'existe pas ERROR_STACK= WF_EVENT_OJMSTEXT_QH.enqueue(APPLSYS.WF_NOTIFICATION_OUT, SQL error is ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT n'existe pas) Wf_Event.Enqueue(oracle.apps.wf.notification.send, WF_EVENT_OJMSTEXT_QH) Wf_Event.Send(oracle.apps.wf.notification.send) Wf_Rule.Default_Rule(oracle.apps.wf.notification.send, C0F79E8FE398266EE0301490C8C41A0C)

       

      we tryed to use those note to recreate wf_notification_out without success

      Doc ID 400493.1

      ORA-20000: Oracle Error Mkr4= -38818 - ORA-38818: illegal reference to editioned object APPLSYS.AQ$_WF_NOTIFICATION_OUT_V

      ORA-06512: at line 13

       

      ::: object APPLSYS.AQ$_WF_NOTIFICATION_OUT_V does not exist in database

      select owner, object_name, object_type, status from all_objects where object_name like '%WF_NOTIFICATION_OUT%';

      APPLSYS AQ$WF_NOTIFICATION_OUT VIEW VALID

      APPLSYS AQ$WF_NOTIFICATION_OUT_S VIEW VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_F VIEW VALID

      APPLSYS WF_NOTIFICATION_OUT TABLE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_S TABLE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_T TABLE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_N SEQUENCE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_H TABLE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_L TABLE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_G TABLE VALID

      APPLSYS AQ$_WF_NOTIFICATION_OUT_I TABLE VALID

       

      [oracle@qued01320 ~]$ sqlplus applsys

      SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 11 07:53:26 2020

      Copyright (c) 1982, 2014, Oracle.  All rights reserved.

      Enter password:

      Last Successful login time: Mon Feb 10 2020 15:32:42 -05:00

      Connected to:

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

      With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

       

      SQL> exec dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT');

      BEGIN dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT'); END;

      *

      ERROR at line 1:

      ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT does not exist

      ORA-06512: at "SYS.DBMS_AQADM", line 464

      ORA-06512: at line 1

       

      SQL> exec dbms_aqadm.drop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT');

      BEGIN dbms_aqadm.drop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT'); END;

      *

      ERROR at line 1:

      ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT does not exist

      ORA-06512: at "SYS.DBMS_AQADM", line 312

      ORA-06512: at line 1

       

      SQL> exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_NOTIFICATION_OUT',force=>true);

      PL/SQL procedure successfully completed.

       

      SQL> 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 others then

      raise_application_error(-20000, 'Oracle Error Mkr2= '

      ||to_char(sqlcode)||' - '||sqlerrm);

      end;

      /

       

      PL/SQL procedure successfully completed.

       

      SQL> begin

        2  dbms_aqadm.create_queue

      (

      queue_name => 'WF_NOTIFICATION_OUT',

      queue_table => 'WF_NOTIFICATION_OUT',

      max_retries => 5,

      retry_delay => 3600,

        3    4    5    6    7    8  retention_time => 86400,

      comment => 'Workflow JMS Topics'

      );

      exception

      when others then

        9   10   11   12   13  raise_application_error(-20000, 'Oracle Error Mkr4= '

      14  ||to_char(sqlcode)||' - '||sqlerrm);

      end;

      / 15   16

      begin

      *

      ERROR at line 1:

      ORA-20000: Oracle Error Mkr4= -38818 - ORA-38818: illegal reference to

      editioned object APPLSYS.AQ$_WF_NOTIFICATION_OUT_V

      ORA-06512: at line 13

       

      Doc ID 736898.1

      ERROR at line 1:

      ORA-20000: Oracle Error = 100 - ORA-01403: no data found

      ORA-06512: at line 104