Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06508: PL/SQL: could not find program unit being called
ORA-06508: PL/SQL: could not find program unit being called [message #179900] Thu, 29 June 2006 04:31 Go to next message
MartinH
Messages: 2
Registered: June 2006
Location: Austria
Junior Member
Hi !

This is my Problem !


I´ve written the following Trigger. In this Trigger I call the package PAC_IFAPP. Everything i change someting in the package an compile it, the trigger return the errormessage -> ORA-06508: PL/SQL: could not find program unit being called.
The Package is in the same Schema as the Trigger, so the must find him. If I start the system new there is no trouble. Cause we use a big ERP System we can start it anytime.
Embarassed
How can I solve this problem. Can anyone help me ?

Triggercode for Row Level:
--------------------------
CREATE OR REPLACE
TRIGGER TRGR_F4211_IFAPP BEFORE INSERT OR UPDATE OF SDLNID,SDLTTR,SDNXTR OR DELETE ON F4211 
FOR EACH ROW 
DECLARE
   tblF4211         CONSTANT VARCHAR2 (20)    := 'F4211';
   strTableStatus   VARCHAR2 (10);
   strDESTAPP       F55100A.IFY55DESTAPP%TYPE := 'xyz';
   strSDCAT         F55100A.IFY55SDCAT%TYPE   := 'xyz';
   tbl_KEY1         F55100C.IFY55KEY1%TYPE    := 0;
   tbl_KEY2         F55100C.IFY55KEY2%TYPE    := 0;
   tbl_KEY3         F55100C.IFY55KEY3%TYPE    := 0;
   tbl_KEY4         F55100C.IFY55KEY4%TYPE    := '0';
   tbl_KEY5         F55100C.IFY55KEY5%TYPE    := '0';
   tbl_KEY6         F55100C.IFY55KEY6%TYPE    := 0;
   tbl_KEY7         F55100C.IFY55KEY7%TYPE    := 0;
   strColumnName    VARCHAR2 (15);
   countF55100C     NUMBER;
   ix               BINARY_INTEGER              := 0;
   bln_OK           BOOLEAN;
   bRet             BOOLEAN;
   lst_Kommbest     pac_ifapp.typ_applist;
   
   
BEGIN
  IF UPDATING THEN
    strTableStatus := 'UPDATE';
  ELSIF INSERTING THEN
    strTableStatus := 'INSERT';
  ELSIF DELETING THEN
    strTableStatus := 'DELETE';
  END IF;


  lst_Kommbest := pac_ifapp.fun_getapplist(tblF4211);    
  

  IF NOT lst_Kommbest.FIRST IS NULL THEN
    FOR countF55100C IN lst_Kommbest.FIRST .. lst_Kommbest.LAST
      LOOP
         bln_OK := FALSE;
         
         
         CASE strTableStatus
            WHEN 'UPDATE' THEN
               strColumnName := RTRIM (lst_Kommbest (countF55100C).ify55colname);
               IF UPDATING (strColumnName) THEN
                  bln_OK := TRUE;
               END IF;
               tbl_KEY1 := :NEW.SDDOCO;
               tbl_KEY2 := :NEW.SDLNID;
               tbl_KEY3 := :NEW.SDNXTR;
               tbl_KEY4 := :NEW.SDCDCD;
               tbl_KEY5 := :NEW.SDLTTR;
            WHEN 'INSERT' THEN
               tbl_KEY1 := :NEW.SDDOCO;
               tbl_KEY2 := :NEW.SDLNID;
               tbl_KEY3 := :NEW.SDNXTR;
               tbl_KEY4 := :NEW.SDCDCD;
               tbl_KEY5 := :NEW.SDLTTR;
               bln_OK := TRUE;
            WHEN 'DELETE' THEN -- kommt in F4211 nie vor
               tbl_KEY1 := :OLD.SDDOCO;
               tbl_KEY2 := :NEW.SDLNID;
               tbl_KEY3 := :NEW.SDNXTR;
               tbl_KEY4 := :NEW.SDCDCD;
               tbl_KEY5 := :NEW.SDLTTR;
               bln_OK := TRUE;
         END CASE;
         
         -- nur einmaliger Eintrag pro Destination App und Stammdatenkategorie
         IF bln_OK THEN
           IF (lst_Kommbest (countF55100C).IFY55DESTAPP != strDESTAPP) OR (lst_Kommbest (countF55100C).IFY55SDCAT != strSDCAT) THEN
               strDESTAPP := lst_Kommbest (countF55100C).IFY55DESTAPP;
               strSDCAT := lst_Kommbest (countF55100C).IFY55SDCAT;
               -- Index für neuen Eintrag
             IF ix = 0 THEN
               ix := 1;
             ELSE
               ix := pac_ifapp.vut_queue (tblF4211).COUNT + 1;
             END IF;
                 
             pac_ifapp.vut_queue (tblF4211) (ix).ify55destapp :=lst_Kommbest (countF55100C).ify55destapp;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55sdcat := lst_Kommbest (countF55100C).ify55sdcat;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55sdvar := 0;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55pkey := 0;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key1 := tbl_KEY1;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key2 := tbl_KEY2;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key3 := tbl_KEY3;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key4 := tbl_KEY4;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key5 := tbl_KEY5;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key6 := tbl_KEY6;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55key7 := tbl_KEY7;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55action := strTableStatus;
             pac_ifapp.vut_queue (tblF4211) (ix).ify55status := 'WAITING';
             pac_ifapp.vut_queue (tblF4211) (ix).ify55info := '0';
             pac_ifapp.vut_queue (tblF4211) (ix).ify55errcode := '';
             pac_ifapp.vut_queue (tblF4211) (ix).ifuser := SUBSTR (USER, 1, 10);
             pac_ifapp.vut_queue (tblF4211) (ix).ifpid := SUBSTR (USER, 1, 10);
             pac_ifapp.vut_queue (tblF4211) (ix).ifjobn :=SUBSTR (USERENV ('TERMINAL'), 1, 10);
    
             bRet := fitools.pac_utility.date_to_jde(SYSDATE,pac_ifapp.vut_queue (tblF4211) (ix).ifupmj,pac_ifapp.vut_queue (tblF4211) (ix).ifupmt); 
                 
           END IF;
         END IF;
      END LOOP;
   END IF;
-- on error
EXCEPTION
   WHEN OTHERS
   THEN
      IF NOT fitools.pac_logs.fun_insert_log ('PRODDTA.F55100LOG',SUBSTR (USER, 1, 30),'TRGR_F4211_IFAPP',SYSTIMESTAMP,'O','E','NULL',SQLCODE,SQLERRM) THEN
         NULL;
      END IF;
END;


Triggercode for Statement Level:
--------------------------------
CREATE OR REPLACE
TRIGGER TRGS_F4211_IFAPP AFTER INSERT OR UPDATE OF SDNXTR OR DELETE ON F4211 
DECLARE
  tblF4211         CONSTANT VARCHAR2 (20) := 'F4211';
  countANZ         NUMBER;
  nmbOrderCount    NUMBER(8);
  bRet             BOOLEAN;
BEGIN
  
         
  IF NOT pac_ifapp.vut_queue (tblF4211) IS NULL THEN
    IF NOT pac_ifapp.vut_queue (tblF4211).FIRST IS NULL THEN
      FOR countANZ IN pac_ifapp.vut_queue (tblF4211).FIRST .. pac_ifapp.vut_queue (tblF4211).LAST LOOP
        
        IF pac_ifapp.fun_chkapprest (pac_ifapp.vut_queue (tblF4211) (countANZ).ify55destapp,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55sdcat,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key1,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key2,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key3,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key4,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key5,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key6,
                                     pac_ifapp.vut_queue (tblF4211) (countANZ).ify55key7) THEN
          
              pac_ifapp.prc_insertappjob(pac_ifapp.vut_queue (tblF4211) (countANZ),TRUE);
        END IF;
      END LOOP;
    END IF;
    
     pac_ifapp.vut_queue (tblF4211).DELETE;
  END IF;
  EXCEPTION WHEN NO_DATA_FOUND THEN 
    NULL;
  WHEN OTHERS THEN
    bRet:= fitools.pac_logs.fun_insert_log ('PRODDTA.F55100LOG',SUBSTR (USER, 1, 30),'TRGS_F4211_IFAPP',SYSTIMESTAMP,'O','E','NULL',SQLCODE,SQLERRM);
 
END TRGS_F4211_IFAPP;


Package Code:
-------------
CREATE OR REPLACE
PACKAGE BODY pac_ifapp
AS
--
--
/******************************************************************************
   FUNCTION:       fun_get_tskey (get timestammp key)
-------------------------------------------------------------------------------
   Erstellt einen PrimaryKey aus sysdate und einer sequence
-------------------------------------------------------------------------------
   PARAMETER:

   RETURN:        String für den PrimaryKey

******************************************************************************/
   FUNCTION fun_getpkey
      RETURN VARCHAR2
   IS
      vnu_seqkey   NUMBER;
      vvc_tskey    VARCHAR2 (18);
   BEGIN
      SELECT seq_pkey.NEXTVAL
        INTO vnu_seqkey
        FROM DUAL;

      vvc_tskey := vvc_tskey || TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS');
      vvc_tskey := vvc_tskey || LTRIM (TO_CHAR (vnu_seqkey, '0000'));
      RETURN vvc_tskey;
   -- on error
   EXCEPTION
      WHEN OTHERS
      THEN
         IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                 SUBSTR (USER, 1, 30),
                                                 'FUN_GETPKEY',
                                                 SYSTIMESTAMP,
                                                 'O',
                                                 'E',
                                                 'NULL',
                                                 SQLCODE,
                                                 SQLERRM
                                                )
         THEN
            RAISE;
         END IF;

         RETURN vvc_tskey;
   END;

--
--
/******************************************************************************
   FUNCTION:       fun_getapplist
-------------------------------------------------------------------------------
   Liest eine Liste der von Applicationen und Colums
   aus der OneWorld Tabelle F55100A

-------------------------------------------------------------------------------
   PARAMETER:
        pvc_tablename   Tabellenname

   RETURN:          Liste der Colums und Applicationen

******************************************************************************/
   FUNCTION fun_getapplist (pvc_tablename IN VARCHAR2)
      RETURN typ_applist
   IS
      i             BINARY_INTEGER := 0;
      vut_applist   typ_applist;

      CURSOR c_applist
      IS
         SELECT *
           FROM f55100a
          WHERE RTRIM (ify55tblname) = pvc_tablename;
   BEGIN
      OPEN c_applist;

      LOOP
         EXIT WHEN c_applist%NOTFOUND;
         i := i + 1;

         FETCH c_applist
          INTO vut_applist (i);
      END LOOP;

      CLOSE c_applist;

      RETURN vut_applist;
   -- on error
   EXCEPTION
      WHEN OTHERS
      THEN
         IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                 SUBSTR (USER, 1, 30),
                                                 'FUN_GETAPPLIST',
                                                 SYSTIMESTAMP,
                                                 'O',
                                                 'E',
                                                 'NULL',
                                                 SQLCODE,
                                                 SQLERRM
                                                )
         THEN
            RAISE;
         END IF;

         RETURN vut_applist;
   END;

--
--
/******************************************************************************
   FUNCTION:       fun_chkapprest
-------------------------------------------------------------------------------
   Überprüft Datenkriterien für die Aktuelle Anwendung

-------------------------------------------------------------------------------
   PARAMETER:
      pvc_destapp   zu prüfende Anwendung
     pvc_sdcat     Stammdatenkategorie

   RETURN:         TRUE: wenn prüfung positiv

******************************************************************************/
   FUNCTION fun_chkapprest (
      pvc_destapp   IN   VARCHAR2,
      pvc_sdcat     IN   VARCHAR2,
      pvc_key1      IN   NUMBER,
      pvc_key2      IN   NUMBER,
      pvc_key3      IN   NUMBER,
      pvc_key4      IN   VARCHAR2,
      pvc_key5      IN   VARCHAR2,
      pvc_key6      IN   NUMBER,
      pvc_key7      IN   NUMBER
      )
      RETURN BOOLEAN
   IS
      tmp_bindvar      VARCHAR2 (20);
      done             BOOLEAN           := FALSE;
      i                NUMBER;
      pos              NUMBER            := 0;
      posx             NUMBER            := 0;
      posy             NUMBER            := 0;
      vvc_sqltxt       VARCHAR2 (1000);
      vbn_result       BOOLEAN;
      cursorid         INTEGER;
      vnu_execresult   INTEGER;
      vrt_apprestr     f55100b%ROWTYPE;
	  
	  
	  
   BEGIN
      -- kriterien aus F55100B lesen
      BEGIN
         SELECT *
           INTO vrt_apprestr
           FROM f55100b
          WHERE ify55destapp = pvc_destapp AND ify55sdcat = pvc_sdcat;

         BEGIN
            vbn_result := TRUE;

            -- 3 mögliche SQL statements
            FOR i IN 1 .. 3
            LOOP
               IF vbn_result = FALSE
               THEN
                  EXIT;
               END IF;

               CASE i
                  WHEN 1
                  THEN
                     vvc_sqltxt := vrt_apprestr.ify55sqltxt1;
                  WHEN 2
                  THEN
                     vvc_sqltxt := vrt_apprestr.ify55sqltxt2;
                  WHEN 3
                  THEN
                     vvc_sqltxt := vrt_apprestr.ify55sqltxt3;
               END CASE;

               -- prüfung ob SQL Statement vorhanden
               IF vvc_sqltxt > ' '
               THEN
                  cursorid := DBMS_SQL.open_cursor;
                  DBMS_SQL.parse (cursorid, vvc_sqltxt, DBMS_SQL.native);
                  posx := 1;
                  posy := 1;
                  done := FALSE;

                  WHILE NOT done
                  LOOP
                     pos := INSTR (vvc_sqltxt, ':', posx);

                     IF pos > 0
                     THEN
                        posx := pos;
                        posy := 5;
                        tmp_bindvar := SUBSTR (vvc_sqltxt, posx, posy);
                        posx := posx + posy;

                        CASE tmp_bindvar
                           WHEN ':KEY1'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, pvc_key1);
                           WHEN ':KEY2'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, pvc_key2);
                           WHEN ':KEY3'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, pvc_key3);
                           WHEN ':KEY4'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, RTRIM (pvc_key4));
                           WHEN ':KEY5'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, RTRIM (pvc_key5));
                           WHEN ':KEY6'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, pvc_key6);
                           WHEN ':KEY7'
                           THEN
                              DBMS_SQL.bind_variable (cursorid, tmp_bindvar, pvc_key7);
                         END CASE;
                     ELSE
                        done := TRUE;
                     END IF;
                  END LOOP;

                  DBMS_SQL.define_column (cursorid, 1, 1);
                  vnu_execresult := DBMS_SQL.EXECUTE (cursorid);

                  --
                  IF DBMS_SQL.fetch_rows (cursorid) = 0
                  THEN
                     vbn_result := FALSE;
                  END IF;

                  DBMS_SQL.close_cursor (cursorid);
               END IF;                                                                        -- end if vvc_sqltxt > ' '
            END LOOP;                                                                            -- for i in 1 .. 3 loop
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               IF DBMS_SQL.is_open (cursorid)
               THEN
                  DBMS_SQL.close_cursor (cursorid);
               END IF;

               vbn_result := FALSE;
            WHEN OTHERS
            THEN
               IF DBMS_SQL.is_open (cursorid)
               THEN
                  DBMS_SQL.close_cursor (cursorid);
               END IF;

               IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                       SUBSTR (USER, 1, 30),
                                                       'FUN_CHKAPPREST',
                                                       SYSTIMESTAMP,
                                                       'O',
                                                       'E',
                                                       'NULL',
                                                       SQLCODE,
                                                       SQLERRM
                                                      )
               THEN
                  RAISE;
               END IF;

               vbn_result := FALSE;
         END;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                    SUBSTR (USER, 1, 30),
                                                    'FUN_CHKAPPREST',
                                                    SYSTIMESTAMP,
                                                    'P',
                                                    'W',
                                                    'NULL',
                                                    SQLCODE,
                                                       'Keine Kriterien in F55100B definiert :'
                                                    || ' DESTAPP: '
                                                    || pvc_destapp
                                                    || ' SDCAT: '
                                                    || pvc_sdcat
                                                   )
            THEN
               RAISE;
            END IF;
      END;

      RETURN vbn_result;
   -- on error
   EXCEPTION
      WHEN OTHERS
      THEN
         IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                 SUBSTR (USER, 1, 30),
                                                 'FUN_CHKAPPREST',
                                                 SYSTIMESTAMP,
                                                 'O',
                                                 'E',
                                                 'NULL',
                                                 SQLCODE,
                                                 SQLERRM
                                                )
         THEN
            RAISE;
         END IF;

         RETURN vbn_result;
   END;

--
--
/******************************************************************************
   FUNCTION:       prc_insertappjob
-------------------------------------------------------------------------------
   Fügt einen Datensatz in die F55100C ein

-------------------------------------------------------------------------------
   PARAMETER:
      prt_appjob   F55100C Datesatz

******************************************************************************/
   PROCEDURE prc_insertappjob (prt_appjob IN OUT f55100c%ROWTYPE, pbn_updatemode IN BOOLEAN)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      vvc_tskey   f55100c.ify55pkey%TYPE;
      rec_count   NUMBER                   := 0;
   BEGIN
      -- aufruf in UpdateMode ??
      IF pbn_updatemode = TRUE
      THEN
         UPDATE f55100c
            SET ify55destapp = prt_appjob.ify55destapp,
                ify55sdcat = prt_appjob.ify55sdcat,
                ify55sdvar = prt_appjob.ify55sdvar,
                ify55key1 = prt_appjob.ify55key1,
                ify55key2 = prt_appjob.ify55key2,
                ify55key3 = prt_appjob.ify55key3,
                ify55key4 = prt_appjob.ify55key4,
                ify55key5 = prt_appjob.ify55key5,
                ify55key6 = prt_appjob.ify55key6,
                ify55key7 = prt_appjob.ify55key7,
                ify55action = prt_appjob.ify55action,
                ify55status = prt_appjob.ify55status,
                ify55info = prt_appjob.ify55info,
                ify55errcode = prt_appjob.ify55errcode,
                ifuser = prt_appjob.ifuser,
                ifpid = prt_appjob.ifpid,
                ifjobn = prt_appjob.ifjobn,
                ifupmj = prt_appjob.ifupmj,
                ifupmt = prt_appjob.ifupmt
          WHERE ify55destapp = prt_appjob.ify55destapp
            AND ify55sdcat = prt_appjob.ify55sdcat
            AND ify55sdvar = prt_appjob.ify55sdvar
            AND ify55key1 = prt_appjob.ify55key1
            AND ify55key2 = prt_appjob.ify55key2
            AND ify55key3 = prt_appjob.ify55key3
            AND ify55key4 = prt_appjob.ify55key4
            AND ify55key5 = prt_appjob.ify55key5
            AND ify55key6 = prt_appjob.ify55key6
            AND ify55key7 = prt_appjob.ify55key7
            AND ify55action = prt_appjob.ify55action
            AND ify55status = prt_appjob.ify55status;

         rec_count := SQL%ROWCOUNT;
      END IF;

      -- Insert
      IF rec_count = 0
      THEN
         prt_appjob.ify55pkey := pac_ifapp.fun_getpkey;

         INSERT INTO f55100c
                     (ify55destapp, ify55sdcat, ify55sdvar, ify55pkey,
                      ify55key1, ify55key2, ify55key3, ify55key4,
                      ify55key5, ify55key6, ify55key7, ify55action,
                      ify55status, ify55info, ify55errcode, ifuser,
                      ifpid, ifjobn, ifupmj, ifupmt
                     )
              VALUES (prt_appjob.ify55destapp, prt_appjob.ify55sdcat, prt_appjob.ify55sdvar, prt_appjob.ify55pkey,
                      prt_appjob.ify55key1, prt_appjob.ify55key2, prt_appjob.ify55key3, prt_appjob.ify55key4,
                      prt_appjob.ify55key5, prt_appjob.ify55key6, prt_appjob.ify55key7, prt_appjob.ify55action,
                      prt_appjob.ify55status, prt_appjob.ify55info, prt_appjob.ify55errcode, prt_appjob.ifuser,
                      prt_appjob.ifpid, prt_appjob.ifjobn, prt_appjob.ifupmj, prt_appjob.ifupmt
                     );
      END IF;

      COMMIT;
   -- on error
   EXCEPTION
      WHEN OTHERS
      THEN
         IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                 SUBSTR (USER, 1, 30),
                                                 'FUN_INSERTAPPJOB',
                                                 SYSTIMESTAMP,
                                                 'O',
                                                 'E',
                                                 'NULL',
                                                 SQLCODE,
                                                 SQLERRM
                                                )
         THEN
            RAISE;
         END IF;
   END prc_insertappjob;
   
/******************************************************************************
   FUNCTION:       prc_insertappjobintoarchive
-------------------------------------------------------------------------------
   Fügt einen Datensatz in die F55100H ein

-------------------------------------------------------------------------------
   PARAMETER:
      prt_appjobintoarchive   F55100H Datesatz

******************************************************************************/
   PROCEDURE prc_insertappjobintoarchive(prt_appjob IN OUT f55100h%ROWTYPE)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      vvc_tskey   f55100h.ify55pkey%TYPE;

   BEGIN
 
     prt_appjob.ify55pkey := pac_ifapp.fun_getpkey;

     INSERT INTO f55100h
                 (ify55destapp, ify55sdcat, ify55sdvar, ify55pkey,
                  ify55key1, ify55key2, ify55key3, ify55key4,
                  ify55key5, ify55key6, ify55key7, ify55action,
                  ify55status, ify55info, ify55errcode, ifuser,
                  ifpid, ifjobn, ifupmj, ifupmt
                 )
          VALUES (prt_appjob.ify55destapp, prt_appjob.ify55sdcat, prt_appjob.ify55sdvar, prt_appjob.ify55pkey,
                  prt_appjob.ify55key1, prt_appjob.ify55key2, prt_appjob.ify55key3, prt_appjob.ify55key4,
                  prt_appjob.ify55key5, prt_appjob.ify55key6, prt_appjob.ify55key7, prt_appjob.ify55action,
                  prt_appjob.ify55status, prt_appjob.ify55info, prt_appjob.ify55errcode, prt_appjob.ifuser,
                  prt_appjob.ifpid, prt_appjob.ifjobn, prt_appjob.ifupmj, prt_appjob.ifupmt
                 );
     COMMIT;

   -- on error
   EXCEPTION
      WHEN OTHERS THEN
         IF NOT fitools.pac_logs.fun_insert_log ('proddta.F55100LOG',
                                                 SUBSTR (USER, 1, 30),
                                                 'FUN_INSERTAPPJOB',
                                                 SYSTIMESTAMP,
                                                 'O',
                                                 'E',
                                                 'NULL',
                                                 SQLCODE,
                                                 SQLERRM
                                                )
         THEN
            RAISE;
         END IF;

   END prc_insertappjobintoarchive;   
-----------------------------------------------------------------------------   
   
END pac_ifapp;



Embarassed

[mod-edit] applied code tags.

[Updated on: Thu, 29 June 2006 04:52] by Moderator

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #180004 is a reply to message #179900] Thu, 29 June 2006 12:34 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
your trigger can only see the code in your package if it's exposed in the package spec. Make sure you have created the packange spec and check for invalid objects.

select OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS 
from user_objects 
where status != 'VALID'


Re: ORA-06508: PL/SQL: could not find program unit being called [message #180058 is a reply to message #180004] Fri, 30 June 2006 01:05 Go to previous messageGo to next message
MartinH
Messages: 2
Registered: June 2006
Location: Austria
Junior Member
Hi Andrew

At first, thanks to you.

The Package Spec is existing. If i check for invalid objects all my triggers are listed. See below.

AUSSCHUSS VIEW 15.07.05 21.11.05 INVALID
DISPOGRAD VIEW 15.07.05 15.07.05 INVALID
FI2TOOW VIEW 15.07.05 15.07.05 INVALID
FI2TOOW1 VIEW 15.07.05 15.07.05 INVALID
KAPAUSLASTUNG VIEW 15.07.05 15.07.05 INVALID
MAGAZINZUGANG VIEW 15.07.05 15.07.05 INVALID
PRESSENPLAN VIEW 15.07.05 15.07.05 INVALID
PRODUKTIVITAET VIEW 15.07.05 15.07.05 INVALID
SETARTIKEL_URLAND VIEW 15.07.05 15.07.05 INVALID
STUECKKOST VIEW 15.07.05 15.07.05 INVALID
TRGR_F01151_IFAPP TRIGGER 09.06.06 26.06.06 INVALID
TRGR_F0115_IFAPP TRIGGER 09.06.06 28.06.06 INVALID
TRGR_F0301_IFAPP TRIGGER 09.06.06 27.06.06 INVALID
TRGR_F4101T_IFAPP TRIGGER 10.06.06 28.06.06 INVALID
TRGR_F4101_IFAPP TRIGGER 12.06.06 28.06.06 INVALID
TRGR_F4104_IFAPP TRIGGER 12.06.06 26.06.06 INVALID
TRGR_F4211_IFAPP TRIGGER 10.06.06 28.06.06 INVALID
TRGR_F4960_IFAPP TRIGGER 12.06.06 28.06.06 INVALID
TRGS_F01151_IFAPP TRIGGER 09.06.06 26.06.06 INVALID
TRGS_F0115_IFAPP TRIGGER 09.06.06 28.06.06 INVALID
TRGS_F0301_IFAPP TRIGGER 09.06.06 27.06.06 INVALID
TRGS_F4101T_IFAPP TRIGGER 12.06.06 28.06.06 INVALID
TRGS_F4101_IFAPP TRIGGER 12.06.06 28.06.06 INVALID
TRGS_F4211_IFAPP TRIGGER 10.06.06 28.06.06 INVALID
TRGS_F4960_IFAPP TRIGGER 12.06.06 28.06.06 INVALID
WIP VIEW 15.07.05 15.07.05 INVALID

26 rows selected
Re: ORA-06508: PL/SQL: could not find program unit being called [message #352731 is a reply to message #179900] Thu, 09 October 2008 04:11 Go to previous messageGo to next message
Mimas
Messages: 40
Registered: September 2008
Location: Athens
Member
Hello all,

I am facing the same error when an update on a package is being made.The the trigger cannot see the changes of the updated package.

Does anyone knows when a package is being updated where in Oracle all these are being stored the changes and the everything before that?

thanks in advance
Mimas
Re: ORA-06508: PL/SQL: could not find program unit being called [message #352737 is a reply to message #352731] Thu, 09 October 2008 04:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you bother to read the link in the answer you got here, before awakening this zombie-thread?

[Updated on: Thu, 09 October 2008 04:49]

Report message to a moderator

Re: ORA-06508: PL/SQL: could not find program unit being called [message #442723 is a reply to message #179900] Wed, 10 February 2010 04:06 Go to previous messageGo to next message
kchandrakishore
Messages: 1
Registered: April 2008
Location: Hyderabad
Junior Member

When I faced this problem, I checked for all procedures status in user_objects, to my surprise I found two packages with the same name one in valid state and another in invalid state. When I dropped the package both of them got dropped and later created the package the problem was resolved.
Re: ORA-06508: PL/SQL: could not find program unit being called [message #442728 is a reply to message #442723] Wed, 10 February 2010 04:31 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Or, more likely, you found two objects, one being the "PACKAGE" tpye, the other being the "PACKAGE BODY" type.
Re: ORA-06508: PL/SQL: could not find program unit being called [message #442730 is a reply to message #442723] Wed, 10 February 2010 04:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, if @Mimas is still waiting after 16 months, then I hope he finds this helpful.


Seriously though - why have you resurrected this zombie?
Previous Topic: matrix multiplication
Next Topic: how to combine all fields in one update statement
Goto Forum:
  


Current Time: Sat Dec 10 20:35:37 CST 2016

Total time taken to generate the page: 0.10344 seconds