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 ?

choochimAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
0
 
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.
0
 
choochimAuthor Commented:
No. The package does not drop/create tables. Although it does drop and create sequence. Is this the reason for this error?
0
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.
0
 
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
0
 
konektorCommented:
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
use:
execute immediate 'select sequence_name.nextval from dual' into program_variable;
instead of
select sequence_name.nextval into program_variable from dual;
0
 
choochimAuthor Commented:
angelIII:

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.
0
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.