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

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 ?

Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
choochimAuthor Commented:
No. The package does not drop/create tables. Although it does drop and create sequence. Is this the reason for this error?
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
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;
choochimAuthor Commented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.