choochim
asked on
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 ?
Can some Oracle PL/SQL guru explain the reason and suggest a remedy to my problem please ?
ASKER
No. The package does not drop/create tables. Although it does drop and create sequence. Is this the reason for this error?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>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.
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
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;
ASKER
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.
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.
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.