[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-12
Medium Priority
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
  • 4
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20066991
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

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20067181
>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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20067194
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20067206

Expert Comment

ID: 20067475
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

ID: 20067604

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month18 days, 18 hours left to enroll

834 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