We help IT Professionals succeed at work.

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

choochim
choochim asked
on
2,099 Views
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 ?

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Commented:
No. The package does not drop/create tables. Although it does drop and create sequence. Is this the reason for this error?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
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;

Author

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.