Why ORA-04068 error when nothing is wrong with the package?

Posted on 2007-10-12
Last Modified: 2013-12-12
I have an error free compiled Oracle package with several procedures. I am trying to call some of these procedures through VBA. The first procedure works fine, but when I try to call the next one, it throws the infamous ORA-04068 error  - " existing state of package is invalid". What gives? The error is fixed once I simply recompile the package. But why in the first place Oracle disturbs the validity of a compiled package between successive procedure calls?

Can some Oracle PL/SQL guru explain the reason and suggest a remedy to my problem please ?

Question by:choochim
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    does the procedures you call try to change / alter / drop / create tables used by the package itself?
    if yes, that is very probably the problem: by running the procedure, it invalidates the package itself.
    you might want to avoid dropping/creating tables (or other objects used by the package), better try to truncate the table rows, for example.

    Author Comment

    No. The package does not drop/create tables. Although it does drop and create sequence. Is this the reason for this error?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Although it does drop and create sequence.
    if the sequence is used in the non-dynamic sql in the package, yes, that is a reason.

    there is a trick to avoid to drop/recreate the sequence:
    query the sequence to get the nextval
    query the all_sequences view to know the maxvalue
    alter the sequence to change the increment by to increment by the difference (maxvalue - nextval)
    query the sequence again to get the nextval
    alter the sequence to change the increment by to increment by 1
    it should start then again by the original start value...

    at least, that is what I remembered reading somewhere... cannot test, don't have oracle here.
    you might need a little fine-tuning...
    I suggest you put that into a function, and use that whenever needed.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    note: the sequence must be created with CYCLE option, if it is created with NOCYCLE, trying to go beyond the MAXVALUE will raise an error
    LVL 142

    Accepted Solution

    LVL 9

    Expert Comment

    when you drop and create cequence again in code - it invalidates all objects which are dependent on it - all object shich uses the sequence.

    do not use sequnce directly in procedure/fuction ... use it dynamicaly instead
    execute immediate 'select sequence_name.nextval from dual' into program_variable;
    instead of
    select sequence_name.nextval into program_variable from dual;

    Author Comment


    Thanks for the help. That seems to do the trick. I still need to reset the sequence for every call to the procedure unfortunately so your trick comes in quite handy. this is something not commonly discussed online I am discovering.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Suggested Solutions

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now