2 Replies Latest reply on Aug 11, 2010 10:17 PM by steven.saunders-Oracle

    Intermittent or Sporadic ORA-00942 and ORA-06512 error during load

    steven.saunders-Oracle
      Oracle 11g R1 on Windows 2003 64-bit Server using 32-bit Instant client ODBC 11g R1.

      Error:
      "DM: : FAP library error: Transaction:<0000004>- area:<ODBC Error>
      code1:<942>- code2:<942>
      msg:<S0002 942 [Oracle][ODBC][Ora]ORA-00942: table or view does not exist
      ORA-06512: at "ORACLE.TRNS_AFTERUPD_TRG"- line 2
      ORA-04088: error during execution of trigger 'ORACLE.TRNS_AFTERUPD_TRG'

      Trigger purpose: Copy column data from active table to a logging table for an ADF web app to use for reporting changes.

      Trigger:
      CREATE TRIGGER TRNS_AFTERUPD_TRG
      AFTER UPDATE ON TRNS
      FOR EACH ROW WHEN (NEW.INUSE=OLD.INUSE OR (NEW.INUSE IS NULL AND OLD.INUSE IS NULL))
      BEGIN
      INSERT INTO TRNSLOG (
      JOB_ID,TRN_ID,UNIQUE_ID,KEY1,KEY2,KEY3,KEYID,FORMSETID,
      ARCKEY,RECTYPE,DESCR,TRNNAME,DOCTYPE,DOCSUBTYPE,ORIGUSER,
      CURRUSER,CURRGROUP,CURRROLE,CURRSUPER,STATUSCODE,TRANCODE,
      APPROVALSTATE,SECLEVEL,TRNERR_ID,INUSE,CUSTOMIZED,CREATETIME,
      BEGINTIME,TRNSTARTTIME,
      MODIFYTIME,
      ARCTIME,RETENTION,TRNENDTIME,
      ENDTIME,JURISDICTN,LOCID,QUEUEID,SUBLOCID,AGENCYID,
      FROMUSER,FROMTIME,FROMGROUP,TOTIME,TOUSER,TOGROUP,
      ROUTEDESC,PROCESSNAME,
      TRNAPPDATE001,TRNAPPDATE002,TRNAPPDATE003,TRNAPPDATE004,TRNAPPDATE005,
      TRNAPPDEC001,TRNAPPDEC002,TRNAPPDEC003,TRNAPPDEC004,TRNAPPDEC005,
      TRNAPPINT001,TRNAPPINT002,TRNAPPINT003,TRNAPPINT004,TRNAPPINT005,
      TRNAPPSTR001,TRNAPPSTR002,TRNAPPSTR003,TRNAPPSTR004,TRNAPPSTR005,
      TRNAPPSTR006,TRNAPPSTR007,TRNAPPSTR008,TRNAPPSTR009,TRNAPPSTR010,
      TRNAPPSTR011,TRNAPPSTR012,TRNAPPSTR013,TRNAPPSTR014,TRNAPPSTR015,
      TRNCUSDATE001,TRNCUSDATE002,TRNCUSDATE003,TRNCUSDATE004,TRNCUSDATE005,
      TRNCUSDATE006,TRNCUSDATE007,TRNCUSDATE008,TRNCUSDATE009,TRNCUSDATE010,
      TRNCUSDATE011,TRNCUSDATE012,TRNCUSDATE013,TRNCUSDATE014,TRNCUSDATE015,
      TRNCUSDEC001,TRNCUSDEC002,TRNCUSDEC003,TRNCUSDEC004,TRNCUSDEC005,
      TRNCUSINT001,TRNCUSINT002,TRNCUSINT003,TRNCUSINT004,TRNCUSINT005,
      TRNCUSSTR001,TRNCUSSTR002,TRNCUSSTR003,TRNCUSSTR004,TRNCUSSTR005,
      TRNCUSSTR006,TRNCUSSTR007,TRNCUSSTR008,TRNCUSSTR009,TRNCUSSTR010,
      TRNCUSSTR011,TRNCUSSTR012,TRNCUSSTR013,TRNCUSSTR014,TRNCUSSTR015,
      TRNCUSSTR016,TRNCUSSTR017,TRNCUSSTR018,TRNCUSSTR019,TRNCUSSTR020,
      ACTION,REASON_ID,TRNSTATUS,TRNRCPTOTAL,TRNRCPPROC,TRNBCHTOTAL,TRNBCHPROC,TRNPRTLOGNAME
      ) VALUES (
      :NEW.JOB_ID,:NEW.TRN_ID,:NEW.UNIQUE_ID,:NEW.KEY1,:NEW.KEY2,:NEW.KEY3,:NEW.KEYID,:NEW.FORMSETID,
      :NEW.ARCKEY,:NEW.RECTYPE,:NEW.DESCR,:NEW.TRNNAME,:NEW.DOCTYPE,:NEW.DOCSUBTYPE,:NEW.ORIGUSER,
      :NEW.CURRUSER,:NEW.CURRGROUP,:NEW.CURRROLE,:NEW.CURRSUPER,:NEW.STATUSCODE,:NEW.TRANCODE,
      :NEW.APPROVALSTATE,:NEW.SECLEVEL,:NEW.TRNERR_ID,:NEW.INUSE,:NEW.CUSTOMIZED,:NEW.CREATETIME,
      :NEW.BEGINTIME,:NEW.TRNSTARTTIME,
      TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP AT TIME ZONE 'GMT','YYYY.MM.DD HH24:MI:SS.FF'),'YYYY.MM.DD HH24:MI:SS.FF'),
      :NEW.ARCTIME,:NEW.RETENTION,:NEW.TRNENDTIME,
      :NEW.ENDTIME,:NEW.JURISDICTN,:NEW.LOCID,:NEW.QUEUEID,:NEW.SUBLOCID,:NEW.AGENCYID,
      :NEW.FROMUSER,:NEW.FROMTIME,:NEW.FROMGROUP,:NEW.TOTIME,:NEW.TOUSER,:NEW.TOGROUP,
      :NEW.ROUTEDESC,:NEW.PROCESSNAME,
      :NEW.TRNAPPDATE001,:NEW.TRNAPPDATE002,:NEW.TRNAPPDATE003,:NEW.TRNAPPDATE004,:NEW.TRNAPPDATE005,
      :NEW.TRNAPPDEC001,:NEW.TRNAPPDEC002,:NEW.TRNAPPDEC003,:NEW.TRNAPPDEC004,:NEW.TRNAPPDEC005,
      :NEW.TRNAPPINT001,:NEW.TRNAPPINT002,:NEW.TRNAPPINT003,:NEW.TRNAPPINT004,:NEW.TRNAPPINT005,
      :NEW.TRNAPPSTR001,:NEW.TRNAPPSTR002,:NEW.TRNAPPSTR003,:NEW.TRNAPPSTR004,:NEW.TRNAPPSTR005,
      :NEW.TRNAPPSTR006,:NEW.TRNAPPSTR007,:NEW.TRNAPPSTR008,:NEW.TRNAPPSTR009,:NEW.TRNAPPSTR010,
      :NEW.TRNAPPSTR011,:NEW.TRNAPPSTR012,:NEW.TRNAPPSTR013,:NEW.TRNAPPSTR014,:NEW.TRNAPPSTR015,
      :NEW.TRNCUSDATE001,:NEW.TRNCUSDATE002,:NEW.TRNCUSDATE003,:NEW.TRNCUSDATE004,:NEW.TRNCUSDATE005,
      :NEW.TRNCUSDATE006,:NEW.TRNCUSDATE007,:NEW.TRNCUSDATE008,:NEW.TRNCUSDATE009,:NEW.TRNCUSDATE010,
      :NEW.TRNCUSDATE011,:NEW.TRNCUSDATE012,:NEW.TRNCUSDATE013,:NEW.TRNCUSDATE014,:NEW.TRNCUSDATE015,
      :NEW.TRNCUSDEC001,:NEW.TRNCUSDEC002,:NEW.TRNCUSDEC003,:NEW.TRNCUSDEC004,:NEW.TRNCUSDEC005,
      :NEW.TRNCUSINT001,:NEW.TRNCUSINT002,:NEW.TRNCUSINT003,:NEW.TRNCUSINT004,:NEW.TRNCUSINT005,
      :NEW.TRNCUSSTR001,:NEW.TRNCUSSTR002,:NEW.TRNCUSSTR003,:NEW.TRNCUSSTR004,:NEW.TRNCUSSTR005,
      :NEW.TRNCUSSTR006,:NEW.TRNCUSSTR007,:NEW.TRNCUSSTR008,:NEW.TRNCUSSTR009,:NEW.TRNCUSSTR010,
      :NEW.TRNCUSSTR011,:NEW.TRNCUSSTR012,:NEW.TRNCUSSTR013,:NEW.TRNCUSSTR014,:NEW.TRNCUSSTR015,
      :NEW.TRNCUSSTR016,:NEW.TRNCUSSTR017,:NEW.TRNCUSSTR018,:NEW.TRNCUSSTR019,:NEW.TRNCUSSTR020,
      :NEW.ACTION,:NEW.REASON_ID,:NEW.TRNSTATUS,:NEW.TRNRCPTOTAL,:NEW.TRNRCPPROC,:NEW.TRNBCHTOTAL,:NEW.TRNBCHPROC,:NEW.TRNPRTLOGNAME);
      END TRNS_AFTERUPD_TRG;
      /