Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: ORA-04068: existing state of packages has been discarded

    Hello:

    Win2k
    Oracle8i 8.1.7

    DBMS_ALERT package was installed
    SQL> @C:\ORACLE\ORA81\RDBMS\ADMIN\DBMSALRT.SQL

    Package created.


    Grant succeeded.


    I verified that the package is valid:

    SELECT status
    FROM dba_objects
    WHERE object_name = 'DBMS_ALERT' AND
    object_type = 'PACKAGE BODY';

    STATUS
    --------------------
    VALID

    I compiled it again:
    SQL> ALTER PACKAGE DBMS_ALERT COMPILE;

    Package altered.


    but everytime I run this PL/SQL block, I get the following error:

    SQL> DECLARE
    2 message VARCHAR2(1800);
    3 status INTEGER;
    4 BEGIN
    5 dbms_alert.REGISTER('emp_change');
    6 dbms_alert.waitone('emp_change',message,status,60) ;
    7 DBMS_OUTPUT.PUT_LINE(message);
    8 dbms_alert.REMOVE('emp_change');
    9 END;
    10 /
    DECLARE
    *
    ERROR at line 1:
    ORA-04068: existing state of packages has been discarded
    ORA-04067: not executed, package body "RICHARD.DBMS_ALERT" does not exist
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at line 5

    any thoughts on how to resolve this????

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    It seems to look for RICHARD.DBMS_ALERT while the package is usually owned by SYS.
    What doe the query

    Select * from dba_objects where object_name = 'DBMS_ALERT'

    give you? Here it gives

    SYS DBMS_ALERT PACKAGE VALID
    SYS DBMS_ALERT PACKAGE BODY VALID
    PUBLIC DBMS_ALERT SYNONYM VALID

    So maybe there's another package DBMS_ALERT around (without a body), owned by RICHARD, because the searching order is:

    OWN OBJECTS
    PRIVATE SYNONYMS
    PUBLIC SYNONYMS

    Hope this helps.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Edwin is correct ...
    @C:\ORACLE\ORA81\RDBMS\ADMIN\DBMSALRT.SQL

    should be run as sys ....
    It will grant execute priv to execute_catalog_role...

    HTH
    Gregg

  4. #4
    Join Date
    Mar 2003
    Posts
    45
    thanks.

    that makes sense since the dbms_alert is ran by catproc as sys, but I ran it again as richard and there may be a discrepency. I will check it out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •