Skip to Main Content
  • Questions
  • Compile procedure automatically -- how to avoid cascading invalidations

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, KK.

Asked: June 08, 2000 - 4:26 am UTC

Answered by: Tom Kyte - Last updated: March 06, 2019 - 11:10 am UTC

Category: Database - Version: 7.3.4 or later

Viewed 50K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Securefile in 12c - part 2

You Asked

I've two procedures:
A calls B to do something. If I compile B, then A will
become invalid. Can I have any setting in the database
in order to compile A automatically when B is compiled?


Thank you for your sincere help!

and we said...

Don't use standalone procedures! Use packages. Packages break the dependency chain.

If procedure A calls procedure B and B is "changed", then A is directly affected and must be recompiled. There is no "auto recompile A when B changes" option -- although A will recompile itself automatically the next time it is run.

On the other hand, If I create a package PKG_A with a procedure A and a package PKG_B with a procedure B then PKG_A will be dependent on PKG_B's specification. I can compile and recompile PKG_B's Body as often as I like without affecting PKG_A's state. As long as PKG_B's specification or interface does not change -- dependent objects are not affected by the recompilation of the body.

Here is a small example showing the differences:



ops$tkyte@8i> create or replace procedure B
2 as
3 begin
4 null;
5 end;
6 /

Procedure created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace procedure A
2 as
3 begin
4 B;
5 end;
6 /

Procedure created.

So procedure A calls B. One of the downsides to procedures, in addition to the direct dependency, is that you have to create them in the "right" order if you want everything valid in the database after an install. I had to create B before A. Packages don't have this nuance either -- as shown below


ops$tkyte@8i> create or replace package pkg_a
2 as
3 procedure a;
4 end;
5 /

Package created.

ops$tkyte@8i> create or replace package pkg_b
2 as
3 procedure b;
4 end;
5 /

Package created.

Here I created the specs for the packages. They are not depenedent on each other and the specs can in fact be created in any order

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body pkg_a
2 as
3 procedure a
4 is
5 begin
6 pkg_b.b;
7 end;
8 end;
9 /

Package body created.

ops$tkyte@8i> create or replace package body pkg_b
2 as
3 procedure b
4 is
5 begin
6 null;
7 end;
8 end;
9 /

Package body created.


There I create the bodies -- I can create the bodies in any order after the specs (in general). PKG_A is dependent on PKG_B's spec -- not its body -- so we don't have an ordering problem...

ops$tkyte@8i>
ops$tkyte@8i> @invalid
ops$tkyte@8i> break on object_type skip 1
ops$tkyte@8i> column status format a10
ops$tkyte@8i> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /

no rows selected

nothing is invalid. Now, lets "reimplement" procedure B in the standalone procedure and in the packaged procedure...

ops$tkyte@8i>
ops$tkyte@8i> create or replace procedure B
2 as
3 begin
4 null;
5 end;
6 /

Procedure created.

ops$tkyte@8i>
ops$tkyte@8i> @invalid
ops$tkyte@8i> break on object_type skip 1
ops$tkyte@8i> column status format a10
ops$tkyte@8i> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /

OBJECT_TYPE OBJECT_NAME STATUS
------------------ ------------------------------ ----------
PROCEDURE A INVALID

Standalone procedure A immediately goes invalid. B was changed and so A needs to be recompiled

ops$tkyte@8i>
ops$tkyte@8i> create or replace package body pkg_b
2 as
3 procedure b
4 is
5 begin
6 null;
7 end;
8 end;
9 /

Package body created.

ops$tkyte@8i> @invalid
ops$tkyte@8i> break on object_type skip 1
ops$tkyte@8i> column status format a10
ops$tkyte@8i> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /

OBJECT_TYPE OBJECT_NAME STATUS
------------------ ------------------------------ ----------
PROCEDURE A INVALID


ops$tkyte@8i>


PKG_A never goes invalid -- never needs to be recompiled since only PKG_B's body was modified. As long as the spec does not change, PKG_A will remain valid


For this reason -- i urge people to only use packages for 'real' code. It can seriously improve performance in a database by reducing or removing the amount of times things get compiled. If you do everything in standalone procedures -- a simple fix to one procedure might make your entire set of routines go invalid (a calls b, c calls a, d calls c and so on. if you update B, a goes invalid -- when a recompiles c will -- then d and so on). Packages can make it so that none of this happen.



and you rated our response

  (147 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

April 16, 2001 - 9:27 pm UTC

Reviewer: Harshan from Cincinnati,OH, USA


July 02, 2001 - 2:39 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia


Well explained

July 17, 2001 - 10:13 am UTC

Reviewer: Kavitha from Birmingham, United Kingdom

The explanation was very clear and most helpful. It gave the most important information in the right order in the right manner.

Thanks a lot!

September 14, 2001 - 10:33 am UTC

Reviewer: Georgi Mechev from Frankfurt, Germany


procedures or packages

September 25, 2001 - 8:12 pm UTC

Reviewer: ajay from india

hi tom,

thanks for ur valuable suggestion its really a good to know such things in that way.

ur explanation with eaxmple is good,


More information

June 26, 2002 - 1:04 pm UTC

Reviewer: Paulo Motta from Rio, Brazil

Does anyone have an additional bibliografy about this stuff ?

Thanks.

Tom Kyte

Followup  

June 26, 2002 - 3:23 pm UTC

Huh?? I looked up that term:

Main Entry: bib·li·og·ra·phy
Pronunciation: "bi-blE-'ä-gr&-fE
Function: noun
Inflected Form(s): plural -phies
Etymology: probably from New Latin bibliographia, from Greek, the copying of books, from bibli- + -graphia -graphy
Date: 1802
1 : the history, identification, or description of writings or publications
2 a : a list often with descriptive or critical notes of writings relating to a particular subject, period, or author b : a list of works written by an author or printed by a publishing house
3 : the works or a list of the works referred to in a text or consulted by the author in its production


what exactly might you be looking for?

If I recompile Pcg, they are working after 2-cnd touch

June 26, 2002 - 9:40 pm UTC

Reviewer: Jan from Slovakia

How we can avoid this without reconnection? Our testing env. is complex and sometimes I need to change some part of code.Ofter I recompile it and run in some session which were using the older version of the code, it will not work, only after 2-cnd calling, what makes data incosintent because only part of the operation is done. How can I avoid that? That users will be not forced to reconnect again.
Thanks,

Tom Kyte

Followup  

June 27, 2002 - 8:05 am UTC

You have to do the double touch on a package like that -- think about it.

The package maintains a state, your user started using it. They have a state established.

You come along and recompile the code -- change it. You have (by definition) wiped out their state. It is gone.

If we did not raise an error, they would blindly go along thinking "everything is OK", when in fact -- their session state was wiped out. The program will be working erroneously at that point. They MUST retry the operation.

You need not "reconnect", you need to catch the error and retry the operation AFTER acknowledging the fact that your state was wiped out. That data inconsistency problem you mention is EXACTLY the reason the error is raised! We give you the opportunity to deal with it (or not -- upto you).



order of the private procedures

October 30, 2002 - 2:58 am UTC

Reviewer: Anil from Hyderabad, India

Comment: This site and your book are really helping me master Oracle.Great help

Version: Oracle version 8.1.7
Problem: PLS-00201: identifier 'SP_Private_proc' must be declared
Explaination: In our package we had three private procedures which have been ordered after all the public procedures have been coded in the package body - so the compile failed. I have reordered the procedures and moved the private procedures to the beginning of the package body it works fine.
Now my question is If in a package we have 50 private procedures and 50 public procedures. Do we need to order the private procedures in the order they are used.OR is there any other way of doing it.?

Thanks & Regards,
Anil


Tom Kyte

Followup  

October 31, 2002 - 9:26 am UTC

use a forward declare:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg
  2  as
  3          procedure public_proc;
  4  end;
  5  /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg
  2  as
  3
  4  procedure private_proc;
  5
  6  procedure public_proc
  7  is
  8  begin
  9          private_proc;
 10  end;
 11
 12  procedure private_proc
 13  is
 14  begin
 15          null;
 16  end;
 17
 18  end;
 19  /

Package body created.


Although my convention is to put ALL private routines at the top and all public routines at the bottom -- but the above works just as well. 

Very clear explanation

October 31, 2002 - 10:10 am UTC

Reviewer: Thiyagarajan from Grand Rapids, MI USA


amusing yourself

October 31, 2002 - 6:06 pm UTC

Reviewer: asktom fanatic from Dayton, OH

I like how you amuse yourself at the expense of our non-english speaking contributors. Your response to the Brazilian surely befuddled him.

any plans for an asktom.grammar.com or asktom.spelling.com site?

Tom Kyte

Followup  

October 31, 2002 - 9:32 pm UTC

Hey -- they confused me as much as they got confused I guess. I really didn't know what they were asking at all (and I do like the site:

</code> http://www.m-w.com/netdict.htm <code>

alot -- use it all of the time...)


I still don't know what they meant by "bibliografy"

Changing to signature can help for remote procedures

November 04, 2002 - 6:51 pm UTC

Reviewer: cachitok@yahoo.com

Because when usign SIGNATURE ( instead TIMESTAMP )

If the timestamp had changed, but the signature (contains timestamp and signature:name of construct, base type of parameters, modes of parameters, number of paramters, etc.) had not changed, the local procedure can execute the remote procedure, while using TIMESTAMP will raise an error.

Now the question I have is why Oracle suggest to use TIMESTAMP instead of SIGNATURE

Thanks

Tom Kyte

Followup  

November 05, 2002 - 9:18 am UTC

where is that suggestion made -- which doc? I want to see it in the context of the surrounding text.

November 05, 2002 - 10:46 am UTC

Reviewer: cachitok@yahoo.com

Where I got it:
TBT: Oracle PL/SQL DB Programming Unit 3 Lesson 2 Topic 4 Page 21.
"If A remote program is changed and recompiled , but the signature does not change, then the local procedure can execute the remote procedure. Using timestamp method, and error will be raised, as the timestamp would not match."

As the question is
A calls B to do something. If I compile B, then A will
become invalid. Can I have any setting in the database
in order to compile A automatically when B is compiled?

This tip helps to avoid the problem of getting an error on a remote procedure.

Did I said something wrong?


Tom Kyte

Followup  

November 05, 2002 - 2:25 pm UTC

I don't know what "TBT" is or whatever but anyway -- my read of that says

"Use SIGNATURE to avoid the error that TIMESTAMP would through". I do not read it as you did -- I do NOT read it as saying "use timestamp, it is better". I read it as "use signature, it avoids the error that would otherwise happen due to a recompilation of the remote object"




Bibliografia

November 05, 2002 - 4:23 pm UTC

Reviewer: Jeremy Smith from Salt Lake City, UT

I'm pretty sure that Paulo Motta is just asking for a list of books and/or documentation that discusses the same type of information you explained to us, i.e., how Oracle goes about deciding what it needs to compile/recompile/invalidate.



Tom Kyte

Followup  

November 05, 2002 - 4:35 pm UTC

Ahh -- ok (i asked him to clarify) anyway - -that would be:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c19depnd.htm#369 <code>

(that pesky server concepts manual again!)


November 05, 2002 - 5:00 pm UTC

Reviewer: cachitok@yahoo.com

Oh, about that....

Really Sorry, I misunderstand what I read in the documentation,

This paragraph,
from "Application Developer’s Guide - Fundamentals"
answers the question.
"
Suggestions for Managing Dependencies
Oracle recommends that you follow these guidelines for setting the
REMOTE_DEPENDENCIES_MODE parameter:
n Server-side PL/SQL users can set the parameter to TIMESTAMP (or let it default
to that) to get the timestamp dependency mode.
n Server-side PL/SQL users can choose to use the signature dependency mode if
they have a distributed system and they want to avoid possible unnecessary
recompilations.
n Client-side PL/SQL users should set the parameter to SIGNATURE. This allows:
– Installation of new applications at client sites, without the need to
recompile procedures.
– Ability to upgrade the server, without encountering timestamp mismatches.
n When using signature mode on the server side, add new procedures to the end
of the procedure (or function) declarations in a package specification. Adding a
new procedure in the middle of the list of declarations can cause unnecessary
invalidation and recompilation of dependent procedures.
"


Object Dependency Checked Every Time Pkg is Called

December 02, 2002 - 10:46 am UTC

Reviewer: Robert from PA

Wow , I wasn't aware of this until it happened - someone dropped a table referenced in a pkg while I was not around during the holiday...

And I thought dependency/references is VERIFIED ONLY at pkg compile time...but now the whole process aborted because of
this...


LMS1@dns > exec tdi_aolw.aolw_test

Hi, this is tdi_aolw.aolw_test, I dont ref any thing !

PL/SQL procedure successfully completed.

/* EMP is NOT referenced by the above (aolw_test)
but by another pkg body program */

LMS1@dns > alter table emp rename to emp2;
Table altered.

LMS1@dns > exit
-- RECONNECT ----
LMS1@dns > exec tdi_aolw.aolw_test
BEGIN tdi_aolw.aolw_test; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "LMS.TDI_REPORT2" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "LMS.TDI_AOLW", line 747
ORA-06512: at line 1

Tom, can you give a little explanation why it appears Oracle
either recompiles(?) or re-validate ALL object references in the entire package everytime any one of the pkg modules
is called,
Thanks

Tom Kyte

Followup  

December 02, 2002 - 12:28 pm UTC

But everything did recompile -- it just failed.

EMP is used by LMS.TDI_REPORT2

LMS.TDI_REPORT2 is called by TDI_AOLW.AOLW_TEST

LMS.TDI_REPORT2 must be available in order to run TDI_AOLW.AOLW_TEST (it calls it after all)

LMS.TDI_REPORT2 is not able to be compiled -- the table EMP it relies on has gone missing.  When TDI_AOLW.AOLW_TEST tried to call it on line 747 -- it failed (the code is *missing* and cannot be recompiled)


Consider this example:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package lms
  2  as
  3          procedure tdi_report2;
  4  end;
  5  /
Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body lms
  2  as
  3          procedure tdi_report2
  4          is
  5          begin
  6                  for x in ( select * from emp )
  7                  loop
  8                          null;
  9                  end loop;
 10          end;
 11  end;
 12  /
Package body created.

<b>so, you have a packge body LMS that is dependent on EMP</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package tdi_aolw
  2  as
  3          procedure aolw_test;
  4          procedure aolw_test2;
  5  end;
  6  /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body tdi_aolw
  2  as
  3          procedure aolw_test
  4          as
  5          begin
  6                  dbms_output.put_line( 'Hello World' );
  7                  lms.tdi_report2;
  8          end;
  9
 10          procedure aolw_test2
 11          as
 12          begin
 13                  dbms_output.put_line( 'Hello World' );
 14          end;
 15  end;
 16  /

Package body created.

<b>and now a package that is dependent on the SPEC of LMS *and* the correct functioning of the body.  </b>

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> @invalid
ops$tkyte@ORA817DEV.US.ORACLE.COM> break on object_type skip 1
ops$tkyte@ORA817DEV.US.ORACLE.COM> column status format a10
ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /

no rows selected

<b>all is valid...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec tdi_aolw.aolw_test2
Hello World

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec tdi_aolw.aolw_test
Hello World

PL/SQL procedure successfully completed.

<b>and every works but...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table emp;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> @invalid
ops$tkyte@ORA817DEV.US.ORACLE.COM> break on object_type skip 1
ops$tkyte@ORA817DEV.US.ORACLE.COM> column status format a10
ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /

OBJECT_TYPE        OBJECT_NAME                    STATUS
------------------ ------------------------------ ----------
PACKAGE BODY       LMS                            INVALID

<b>we dropped emp (you renamed it, same thing) and the body of LMS went invalid and cannot be recompiled</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec tdi_aolw.aolw_test2
Hello World

PL/SQL procedure successfully completed.

<b>that works -- TDI_AOLW is dependent on the spec of LMS, the spec is valid, we didn't actually CALL LMS so no error.  But</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec tdi_aolw.aolw_test
BEGIN tdi_aolw.aolw_test; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "OPS$TKYTE.LMS" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "OPS$TKYTE.TDI_AOLW", line 7
ORA-06512: at line 1

<b>that of course MUST fail since LMS is invalid and cannot possibly recompile itself, parts of what it needs have gone missing</b>
 

Right but if I call a pkg module that depends on nothing, nada....

December 02, 2002 - 2:26 pm UTC

Reviewer: Robert from PA

>> that of course MUST fail since LMS is invalid and cannot >> possibly recompile
>> itself, parts of what it needs have gone missing

Right, but in your example Procedure aolw_test
calls a module which "really, really" depends on the object from the get-go. Oracle complains when the obj is not there, that's reasonable...

What caught me off-guard is the fact that even if
I call a single module in LMS that depends on NOTHING, Oracle errrors out on me , that is --- the entire LMS pkg is unusable if *any* table referenced *anywhere* in the pkg is missing, this sums it up right ?
Thanks


Tom Kyte

Followup  

December 02, 2002 - 2:29 pm UTC

yes, the entire LMS package body either

o can compile
o cannot compile

the dependency is at the OBJECT level and the object here is the package body itself.

Individual routines in LMS are not compiled -- LMS is the compilation unit. It either entirely compiles, or as in your case -- NOT.

Compile procedure automatically

December 02, 2002 - 4:36 pm UTC

Reviewer: Steve Booth from Waukesha, WI USA

I've been using Packages but am having a situation where the code needs to be recompiled (doesn't seem to happen automatically) when a partition is dropped or added. Any ideas?

TIA

Tom Kyte

Followup  

December 02, 2002 - 5:25 pm UTC

got an example?

packages

January 03, 2003 - 10:01 am UTC

Reviewer: mo

Tom:

IF you have a web application with 100 stored procedures and does not use packages and you are using SQL Navigator to create those:

1. How do you decide how many packages to create. Would you create one package per one procedure or you would bundle several procedures into one package or it does not really make a difference.

2. If procedures do not call each other would it still make sense to use packages since you have not listed another reason for the advantages other than the "invalidation" and "recompilation".

3. If I create the packages, then you would be editing the code of package body and not touch the procedure code at all listed under Procedures in navigator?

4. Do you use any naming convention for name of package. i.e same as procedure PKG_PROC_NAME.

Thank you,

Tom Kyte

Followup  

January 03, 2003 - 10:11 am UTC

1) use common sense, that is all. group related functions together logically. If it boils down to 1 procedure/package -- so be it (in fact, you would probably find that your procedures are larger then a screen, that is you have to page up/down through them. If so - use the fact you are in a package to MODULARIZE your code -- break it into several private routines inside the package body to make it more readable, more maintainable).


2) packages are good. packages have many features -- you would benefit from having those private procedures so you wouldn't be afraid to go crazy and write a couple of subroutine to break an otherwise monolithic routine into several smaller routines -- something you would be hesitant to do if you use standalone procedures

3) i use sqlplus. i do not use "navigator". i have no idea what I would do in navigator.

4) something_pkg where something represents what the package is all about.

Same or different package

January 06, 2003 - 7:27 pm UTC

Reviewer: sean from NJ, USA

Hi Tom,

I have an application which has about 80 stored procedures in one schema without using packages. Now I am going to add package to each stored procedure.

Some of the stored procedure are related, such as A calls B and C, and C calls D. Do I have to put these procedures in one package?

It will be nice if you can give me some examples of procedures using scott schema and then put them in different or same packages according to the principles you defined.

Thanks,

Best,

Sean


Tom Kyte

Followup  

January 07, 2003 - 6:07 am UTC

You don't have to -- you would put procedures together in a package because they are RELATED something -- eg: p1 inserts into T, p2 updates T, p3 deletes from T -- p1, p2, p3 should probably be together.

Common sense rules here -- do what feels right, group them together based on functionality. Take advantage of the fact that they are in a package. Many times I see procedures that are bigger then I would have a package be! Take the time not only to put into a package but to modularize the existing code. if you have a stored procedure more then 60-80 lines long - break it up into many smaller procedures. put them all in a package -- you only have to "export" the definition of the one procedure the rest can (should) remain private

January 07, 2003 - 3:01 pm UTC

Reviewer: cachitok@yahoo.com

If you have not too much experience letme giveme some suggestions to avoid problems:

If you are going to group as much procedures
I suggest you that clearly in the package body set the limit between procedures.
for example
---------------------------------------------
**********************************************
---------------------------------------------

Don't group too much procedures in only one package if you are modifying frequently. Debugging could be some difficult.


Remember that everywhere you call them, you must change the calling name. Test every thing.

Don't forget you can use share private and global variables in the package. that at the end of the package you can initialize variables, etc. That could help to optimize and simplify your process, check the documentation.
Packages are really excellent but you need to take the time to learn about it.

Most of the problems Solved

March 14, 2003 - 5:02 am UTC

Reviewer: Ashok Shinde from India

Tom,

Following your suggestion we modified most the code so that we are no longer dependent on the procedure compilation resulting in others becoming invalid. But we have a package body with is called from the web server. This package body becomes invalid (leaving package specifications valid) whenever it is called from web server. Once we bounce and restart the web server the body becomes valid. We are not able to trace why this package body becomes invalid. This package refers some remote database tables which have select privileges granted to public.

Can you please explain me what would cause this package body to become invalid ? And why it is not compiled whenever the any procedure in the body is called ?

Regards,
Ashok Shinde.

Tom Kyte

Followup  

March 14, 2003 - 5:59 pm UTC

look in dba_dependencies, see what it is dependent on.

State of the package when auto compile

August 29, 2003 - 10:44 am UTC

Reviewer: A reader

Tom,

Your test case in response to Robert  from PA .

I slightly changed the package to add a state to it. 



create or replace package lms as
                        procedure tdi_report2;
       y number(2):=0;
       end;

Package created.      



create or replace package body lms
    as
            procedure tdi_report2
            is
            begin
                    for x in ( select * from T1 )
                    loop
                            null;
                    end loop;
      y:=y+1;

           end;
          end;
 


In session-2:

SQL> set serveroutput on size 100000
SQL> exec tdi_aolw.aolw_test2;
Hello World

PL/SQL procedure successfully completed.

SQL> exec tdi_aolw.aolw_test;
Hello World

PL/SQL procedure successfully completed.


In session-1:

SQL> alter table t1 add partition p2 values less than (to_date('01-feb-2003','dd
-mon-yyyy'));

Table altered.


In Session-2:



SQL> select object_type, object_name, status
    from user_objects
    where status = 'INVALID'
    order by object_type, object_name;
  2    3    4  PACKAGE BODY
LMS
INVALID


SQL> exec tdi_aolw.aolw_test;
Hello World

PL/SQL procedure successfully completed.

SQL> exec tdi_aolw.aolw_test2;
Hello World   ---> No Error Here.


However:


When i do the below:


if in session-1:


SQL> alter table t1 drop partition p2;

Table altered.

SQL> alter package lms compile body; --> The state of the package is discarded.

Package body altered.


Then when running Session -2:



SQL> exec tdi_aolw.aolw_test;
BEGIN tdi_aolw.aolw_test; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SCOTT.LMS" has been invalidated
ORA-04065: not executed, altered or dropped package body "SCOTT.LMS"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SCOTT.TDI_AOLW", line 7
ORA-06512: at line 1


SQL> exec tdi_aolw.aolw_test2;

PL/SQL procedure successfully completed.   


Why do we not get 4068 error in case-1? What is the state of global variable "y" in case-1?

Thanks, 

Tom Kyte

Followup  

August 29, 2003 - 11:00 am UTC

me thinks you did something wrong....

the alter add did not invalidate for me.

the alter drop did

your names don't match up, incomplete test case -- show us the "real" stuff.

Complete case:

August 29, 2003 - 11:15 am UTC

Reviewer: A reader

Tom,

SQL> create table t1(id number, dt1 date)
  2   partition by range(dt1)
  3  (partition p1 values less than (to_date('1-jan-2003','dd-mon-yyyy')),
  4  partition p2 values less than (to_date('2-feb-2003','dd-mon-yyyy')));
  

  create or replace package lms as
                        procedure tdi_report2;
       y number(2):=0;
       end;



create or replace package body lms
    as
            procedure tdi_report2
            is
            begin
                    for x in ( select * from T1 )
                    loop
                            null;
                    end loop;
      y:=y+1;

           end;
          end;
 


create or replace package tdi_aolw
    as
            procedure aolw_test;
            procedure aolw_test2;
    end;
  



create or replace package body tdi_aolw
    as
            procedure aolw_test
            as
            begin
                    dbms_output.put_line( 'Hello World' );
                    lms.tdi_report2;
            end;
  
           procedure aolw_test2
           as
           begin
                   dbms_output.put_line( 'Hello World' );
           end;
   end;
 

Session-1:


SQL> select object_type, object_name, status
    from user_objects
    where status = 'INVALID'
    order by object_type, object_name;
  2    3    4
no rows selected             


Session-2:

SQL> select object_type, object_name, status
    from user_objects
    where status = 'INVALID'
    order by object_type, object_name;
  2    3    4
no rows selected

Elapsed: 00:00:00.01 


Session-1:

SQL> alter table t1 add partition p2 values less than (to_date('01-feb-2003','dd
-mon-yyyy'));

Table altered.

Elapsed: 00:00:00.11  


Session-2:

SQL> /
PACKAGE BODY
LMS
INVALID


Elapsed: 00:00:00.00   

In Session-2:

SQL> exec tdi_aolw.aolw_test;
Hello World

PL/SQL procedure successfully completed.

SQL> exec tdi_aolw.aolw_test2;
Hello World   ---> No Error Here.


However if i compile body i get 4068 error.

I am doing something wrong here.

Thanks in advance.




 

Tom Kyte

Followup  

August 29, 2003 - 12:23 pm UTC

so, the package didn't have a state yet? no problem.

RE: Alter table add partition...

August 29, 2003 - 12:15 pm UTC

Reviewer: Mark A. Williams from Indianapolis, IN USA

How are you adding the table partition?  Why is it named the same as an existing partition?

SQL> alter table t1 add partition p2 values less than (to_date('01-feb-2003','dd-mon-yyyy'));
alter table t1 add partition p2 values less than (to_date('01-feb-2003','dd-mon-yyyy'))
                             *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

Just curious...

- Mark 

What is a package state?

August 29, 2003 - 1:34 pm UTC

Reviewer: A reader

Tom,

What is a package state? What defines a package state? when will the package state change?

Mark,

I had a step before my test where i dropped the partition p2.

Thanks,

Tom Kyte

Followup  

August 29, 2003 - 2:51 pm UTC

create or replace package lms as
procedure tdi_report2;
y number(2):=0; <<<<=== package state, global variable

end;

changing repository on a database

October 17, 2003 - 5:58 pm UTC

Reviewer: A reader

Hi Tom
I am going to recreate schema and pl/sql packages
related to a very small independent functionality in a production
database (minor downtimes are ok). The middletier
uses one of these packages to get the data (the middle
tier code does not change as we don;t change
the "select clause" in the package used by middle tier.
My question is:
How do I avoid the existing state problem in the
package? Can I exercise the middle tier code twice
(that means visit the page twice) for it to start
working. I dont want to bounce the app server on
which the middle tier runs.

Tom Kyte

Followup  

October 19, 2003 - 5:49 pm UTC

depends on how robust the middle tier is at handling errors -- and in general, no, visting the page 2 times won't fix it as you have to hit EACH cached connection.


If you hit this error with a middle tier app, you probably have bigger bugs to fry anyway. that means your connection pool is actually maintaining a connected state and NOT reseting it in the database! that means you can grab a connect that has "leftovers" from my previous connection. not a very good thing.

thanx Tom!

October 20, 2003 - 1:55 pm UTC

Reviewer: A reader

"depends on how robust the middle tier is at handling errors -- and in general,
no, visting the page 2 times won't fix it as you have to hit EACH cached connection."

Excellent point! However, how does the middle tier
handle this error - should it catch the error and
issue "Recompile" of the packages - that would be
too tedious, no?

"If you hit this error with a middle tier app, you probably have bigger bugs to
fry anyway"

Also what did you mean by the above point.
Do you mean that the middle tier should work
flawlessly regardless of if I change the pl/sql
package in without bouncing the app server?
I think perhaps you are advocating a "stateless"
middle tier?


Tom Kyte

Followup  

October 20, 2003 - 2:22 pm UTC



it doesn't have to "recompile the packages", it just must understand that "the package it was using, which maintained a state, has had the state destroyed". It is up to the application (middle tier) to decide if

a) this is OK
b) this is fatal


If you have a connection pool -- you DARN SURE BETTER have a stateless middle tier as far as the database is concerned. You

a) grab connection
b) make a page
c) give up connection
d) goto a) when user does something

each time you goto a) you are grabbing a new connection. If you maintain a state in that database connection -- you have really unreliable behaviour as you'll get a DIFFERENT state potentially each time, worse -- someone else will get YOUR state!

(and yes, I advocate a stateless middle tier regardless, I never maintain a state in the middle tier)

thanx Tom!!

October 20, 2003 - 3:58 pm UTC

Reviewer: A reader


In 9i Release 2 : Removes double invalidations ??

October 20, 2003 - 10:07 pm UTC

Reviewer: Raj.N

What is Double invalidation?.Not able to find more information about this statement.I hope my question is related to this thread.Can you please provide little more explainations.

I am refering to whitepaper : </code> http://otn.oracle.com/products/oracle9i/pdf/9idb_rel2_features.pdf <code>

Speed up of application and database upgrades

"provides fast loading of wrapped source, knows to do nothing upon loading an unchanged package ,
view or synonym, removes double invalidations and does parallel compilation in dependency order."

Tom Kyte

Followup  

October 21, 2003 - 7:10 am UTC

it is just saying "we can, during upgrades, detect that stuff didn't really change and avoid tons and tons of work".

that is all -- it just knows that something that was recompiled didn't really change and skips tons of work.

No implied dependences b/w package specs?

October 27, 2003 - 1:56 pm UTC

Reviewer: Dan Loomis from Raleigh, NC

Tom - we use packages here exclusively for the many benefits they provide over standalone functions/procedures. One such benefit is they break the dependency chain, but we do have one situation where that is not the case.

13:49:32 apps@CTSGLD> create or replace package test_constants as
13:52:55 2
13:52:55 3 g_some_value CONSTANT varchar2(10) := 'SOME VALUE';
13:52:55 4
13:52:55 5 end;
13:52:55 6 /

Package created.

13:52:55 apps@CTSGLD>
13:52:55 apps@CTSGLD> create or replace package test_package as
13:52:55 2
13:52:55 3 procedure test (
13:52:55 4 p_input IN VARCHAR2 DEFAULT test_constants.g_some_value
13:52:55 5 );
13:52:55 6
13:52:55 7 end;
13:52:55 8 /

Package created.


Now if I drop the first package spec, the other immediately is invalidated, for obvious reasons. We leverage package specs for constants all the time, so the dependency issue still exists for us.

Tom Kyte

Followup  

October 27, 2003 - 6:30 pm UTC

that is a direct dependency.  If you did something like this:

ops$tkyte@ORA920> create or replace package test_constants
  2  as
  3          function get_str_val( p_name in varchar2 ) return varchar2;
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package body test_constants
  2  as
  3          type array is table of varchar2(255) index by varchar2(30);
  4          g_data array;
  5
  6          function get_str_val( p_name in varchar2 ) return varchar2
  7          is
  8          begin
  9                  return g_data(p_name);
 10          end;
 11
 12  begin
 13          g_data( 'SOME_VALUE' ) := 'Some Value';
 14  end;
 15  /
 
Package body created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package test_pkg
  2  as
  3
  4          procedure test( p_input in varchar2
  5             default test_constants.get_str_val( 'SOME_VALUE' ) );
  6
  7  end;
  8  /
 
Package created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package body test_pkg
  2  as
  3
  4          procedure test( p_input in varchar2
  5             default test_constants.get_str_val( 'SOME_VALUE' ) )
  6      is
  7          begin
  8                  dbms_output.put_line( p_input );
  9          end;
 10
 11  end;
 12  /
 
Package body created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec test_pkg.test
Some Value
 
PL/SQL procedure successfully completed.


you can avoid that cascading invalidation at the slight cost of invoking a function for the defaults. 

Cannot trap ora-04068 with exception handler

October 27, 2003 - 4:07 pm UTC

Reviewer: Henry from South Kingstown, RI USA

Tom,
Previously in this thread you said, with respect to loss of state (ora-04068), that "you need to catch the error and retry the operation AFTER acknowledging the fact that your state was wiped out...We give you the opportunity to deal with it" (from June 26, 2002).

I have been trying, unsuccessfully to trap the 4068 (using version 8.1.7.4). A search through Metalink didn't reveal any bugs, but there were some indirect comments which alluded to the fact that maybe this error couldn't be trapped. (these were more along the lines of omitting this method as a solution as opposed to directly stating it couldn't be done)

Here are my test package spec and package body.

create or replace package state_test as
procedure increment;
procedure show;
procedure independent;
end;
/

create or replace package body state_test as
y number(2) := 0;
state_lost exception;
pragma exception_init(state_lost, -04068);

procedure increment
as
begin
y:=y+1;
end;

procedure show
as
begin
dbms_output.put_line('y = '||y);
exception
WHEN state_lost THEN
dbms_output.put_line('It is an error');
end;

procedure independent
as
d number;
begin
select * into d from state_test_tbl;
end;
end;
/

I then:
1. ran state_test.show with one session
2. dropped and recreated state_test_tbl (to invalidate my package body)
3. ran state_test.show from another session (to recompile my invalidated package body and wipe out the initial state)
4. ran state_test.show from my original session

When I did this, I got

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYSTEM.STATE_TEST" has been
invalidated
ORA-04065: not executed, altered or dropped package body "SYSTEM.STATE_TEST"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1

What am I missing here?

Thanks.

Henry


Tom Kyte

Followup  

October 27, 2003 - 6:45 pm UTC

the client catches this. there is nothing to "catch" in the code above -- the code was *WIPED OUT* -- it isn't runnable, it isn't running. You never get within a mile of the exception block, it was "erased from memory"

Table Dependency

November 30, 2003 - 4:21 pm UTC

Reviewer: Robert from CT

Is there a way to find out what tables/views a package references ?

Thanks

Tom Kyte

Followup  

November 30, 2003 - 7:44 pm UTC

user|all|dba_dependencies

(a view)

How many packages

December 16, 2003 - 2:11 pm UTC

Reviewer: B from New Jersey

Hi Tom,

Someone asked you:
"How do you decide how many packages to create. Would you create one package per one procedure or you would bundle several procedures into one package or it
does not really make a difference."

And you answered:
"use common sense, that is all. group related functions together logically. If it boils down to 1 rocedure/package -- so be it (in fact, you would probably find that your procedures are larger then a screen, that is you have to page up/down through them. If so - use the fact you are in a package to MODULARIZE your code -- break it into several private routines inside the package body to make it more readable, more maintainable)."

We currently do not have any packages. We have about 20 procedures and 3 functions. Now our manager has asked us to place all the pieces of code into one package. That way everything will be in one place. But if we make a change to any procedure the entire code will be migrated to production.

I know you do not have any hard nad fast rules of how to group procs and functions but is that the best option to put everything into one package? I cannot think of any good reason why not other than it will be more difficult to scroll through 20 pieces of code to debug than one piece of code.

Thank you for any advice you could give me!





Tom Kyte

Followup  

December 16, 2003 - 2:39 pm UTC

while I'm against "put them all in one just because" (there should be some thought put into it) what is wrong with having one package to zap into production? I don't see any fundemental issues there? what is your concern?

the number of procedures to put into a package is somewhere between 1 and infinity.

You debug SMALL, modular routines. I'll betcha your code in the standalong routines does not "fit on the screen". this'll be the perfect opportunity to make each procedure about 60-80 lines long max -- making the code infinitely more maintainable, understandable and debuggable.

Hopefully you'll go from 20 top level procedures to a package with 20 exposed procedure and 40 hidden ones (40 is just a number, use any number you want)

OK

December 16, 2003 - 2:53 pm UTC

Reviewer: B from New Jersey

We have large procedures beacuse we have large queries that return a lot of data.

But I understand your point that this is an opportunity to see if we can make our code even more modular.

Thanks.

Tom Kyte

Followup  

December 16, 2003 - 5:41 pm UTC

(views = way to make large queries seem really small)

think of a view like a subroutine

Package state

January 25, 2004 - 11:40 pm UTC

Reviewer: Dale Ogilvie from New Zealand

What events cause package state (variables) to be wiped out?

I know that if I make a change to a package then the resulting recompile kills my package state, but it would seem that a simple compile without changing the package does not. We are seeing invalidation of package state on production and I'd like to know what might be causing it. We flush the shared pool each night, but my testing seems to show that this does not cause invalidation of package state.

Also, how come this line in my package body causes my package to have state, I would have expected a constant to not be "state". If I change the function getdefs below, I get the dreaded invalid state error *only* if I have the constant in the body.

evt_xyz CONSTANT NUMBER := 84;

The full body def:

PACKAGE BODY PKG_X as

evt_start_moving CONSTANT NUMBER := 95;

procedure getdefs(defs out sys_refcursor)
is
begin

OPEN defs FOR
select
'dale'
from
dual;

end;

end;

thanks

Dale

Tom Kyte

Followup  

January 26, 2004 - 6:12 am UTC

only a call to dbms_session.reset_package or a recompilation would -- and the recompilation would return "sorry, package state invalidated"


The constant causes that cause the constant could well have become "not so constant" due to a recompilation. One time it could have been 95, the next 55 -- we have no way of knowing (don't track things at that level) hence -- you have a state and you've potentially destroyed it.

But recompiles alone don't kill state

January 26, 2004 - 3:38 pm UTC

Reviewer: Dale Ogilvie from New Zealand


Thanks, just to confirm though. If I do a recompile (without actually changing the package body) my state survives.

nccdb@P1ZUSD> alter package pkg_x compile body;

Package body altered.

nccdb@P1ZUSD> exec pkg_x.getdefs( :ignitions );

PL/SQL procedure successfully completed.


'DAL
----
dale


making procedures invalid voluntarily

March 09, 2004 - 4:00 pm UTC

Reviewer: Pushparaj Arulappan from NJ USA

Tom,

Is there a way to make all the dependent stored
programs of a table invalid without dropping or
renaming the table.

Thanks
Pushparaj Arulappan

Tom Kyte

Followup  

March 09, 2004 - 10:25 pm UTC

whats the goal? (eg: why?)

in short, no, but what are you trying to do?

March 25, 2004 - 3:07 pm UTC

Reviewer: A reader

I have a package that defines all the constants, and all other packages reference the constants defined in this constant_pkg.

If I add a new constant to constant_pkg, and then recompile all packages and verify that all packages are "VALID", execute another package that references this constant_pkg, I will get

ORA-04061: existing state of has been invalidated
ORA-04061: existing state of package "HCSA1.CONSTANT_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package "HCSA1.CONSTANT_PKG"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "HCSA1.HOUSEHOLD_PKG", line 320
ORA-06512: at "HCSA1.HOUSEHOLD_PKG", line 1354

What can I do to avoid this problem?


Tom Kyte

Followup  

March 25, 2004 - 9:23 pm UTC

schedule system upgrades during periods of little to no activity.

ask yourself "if I had to patch a DLL on windows, a .so file on Unix, a binary on any OS -- what would I need to do" that in effect is what you are doing here....

ORA-04061 --> if in different sessions..

May 11, 2004 - 2:12 pm UTC

Reviewer: Mohini from Australia

--ORACLE 9.2.0.5

Tom,

You said..in your response
"schedule system upgrades during periods of little to no activity."

Here is my example...
If I run Step 1, 2 and 3 all in the same session..I don't get the error..
but if I run step 1 and 2 in the same session but step 3 in another session..
I get "invalid state error"..the first time..and then second time it works fine.
This happens..even when I have compiled the dependent procedure before running it..

So, questions:
1. Even if I do step 1 and 2 during low activity..
step 3, whenever it is run, is going to give an error the first time..
cause it won't be in the same session..
Is there a better design that I could go with for these constant declarations.

2. After creating a package spec..with constants/ types etc.
does it matter if we shuffle the order they appear in the spec...or is
it same as recreating the spec...

CREATE OR REPLACE PACKAGE TYPES_PKG
AS

--TYPES
TYPE REFCUR_T IS REF CURSOR;

END TYPES_PKG;
/


CREATE OR REPLACE PACKAGE CONSTANTS_PKG
AS

MY_TAB VARCHAR2 (30) := 'EMP';

END CONSTANTS_PKG;

/

CREATE OR REPLACE PROCEDURE GET_EMP (C_CUR OUT TYPES_PKG.REFCUR_T)
AS

BEGIN

OPEN C_CUR FOR 'SELECT * FROM ' || CONSTANTS_PKG.MY_TAB || ' WHERE ROWNUM < 5';

END GET_EMP;

/

SET LINESIZE 1024
SET PAGESIZE 1024
set serveroutput on size 1000000
var r refcursor
set autoprint on

EXEC GET_EMP (:R);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN ...........



/*****************STEP 1********************/

--RECREATING CONSTANTS_PKG
CREATE OR REPLACE PACKAGE CONSTANTS_PKG
AS

MY_TAB VARCHAR2 (30) := 'EMP';

END CONSTANTS_PKG;

/

/*****************STEP 2********************/

--COMPILING DEPENDENCIES
ALTER PROCEDURE GET_EMP COMPILE;

/*****************STEP 3********************/

EXEC GET_EMP (:R);


BEGIN GET_EMP (:R); END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "CONSTANTS_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package "MICKEYDBA.CONSTANTS_PKG"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "GET_EMP", line 6
ORA-06512: at line 1


ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "r"


Thanks.






Tom Kyte

Followup  

May 11, 2004 - 3:21 pm UTC

why are you rebuilding specs in a production environment? isn't that in the realm of "really dangerous" (rhetorical question).

rebuilding a spec is a major application update. period. should be done during a scheduled maintanence windows.

Sort of like trying to replace a running executable (page fault)

or a loaded jar file (confusion reigns)



rebuiding spec

May 11, 2004 - 4:02 pm UTC

Reviewer: mohini from australia

True...We should build the specs in production during low activity... (Recreating the package and compiling invalids)
but the dependent proc will run at a later time..and the first time it will run..will it get the invalid package state error.
If yes...then app users will seeing those messages too...
So, should we use something else instead of global packaged variables...to cover for this issue...
Your take on this??

Thanks.

Tom Kyte

Followup  

May 11, 2004 - 8:49 pm UTC

when you upgrade your system to version N+1, you will need a pause.

You'll have this "variable" issue AT SOME LEVEL if you think about it. replace code = loss of state = gotta stop for a second. i don't care which of 14 tiers you do it on, loss of state, when state = mandatory, equals "gotta stop"

invalid state

May 12, 2004 - 9:09 am UTC

Reviewer: mohini from australia

Tom,

This is a overnight load process.
So, a dependent procedure will fail with an error the first time and then succeeds the second time (assuming the package the procedure depends upon is recreated at some point).
If this is true...then this overnight load process will come to halt at night...so is the solution to avoid this first time failure
is to physically execute the dependencies once (after the package recreation) so that the invalidate package state error doesn't happen at night? True?
Or is there a way to cover for this error before hand without actually executing the dependencies (considering Just compiling the procedure again does not help)

Thanks

Tom Kyte

Followup  

May 12, 2004 - 6:46 pm UTC

not following your thread here.


what is happening that is forcing you to recompile a spec.

May 13, 2004 - 9:16 am UTC

Reviewer: mohini from australia

--RECREATING CONSTANTS_PKG
CREATE OR REPLACE PACKAGE CONSTANTS_PKG
AS

MY_TAB VARCHAR2 (30) := 'EMP';

END CONSTANTS_PKG;

/


Global variables are in a spec..so if there is any change/modifications ..the spec has to be recreated....

Tom Kyte

Followup  

May 13, 2004 - 10:50 am UTC

and as I keep saying "that would be a system upgrade", like trying to replace a running binary - like trying to upgrade the OS while it is running.

You need to schedule that during a maintenance window.

Problem with compiling remote dependancy

May 24, 2004 - 2:08 pm UTC

Reviewer: JB from Cincinnati

I have a package (PKG_test_remote) on DB 1. A procedure (PRC_test_remote_call) on DB 2 calls a function (function 1) within PKG_test_remote on DB 1. If a function is added/removed from PKG_test_remote, the next time the PRC_test_remote_call on DB 2 calls function 1 it fails with an ORA-04068 "the existing state of the package has been discarded."
When I change PKG_test_remote (i.e. add/remove a procedure so that the spec changes), I do not want to have my remote procedures fail, nor do I want to manually recompile each one after the package has been changed. I have tried to trap the -04068 error, but the error is still being raised. Likewise, i have even tried to recompile PRC_test_remote_call before it calls PKG_test_remote.function1, but this does not produce any better results. How do I program my remote procedures so that they recover from this error and succeed?
Thanks,
JB
Attached is the sample code I have been working with:
PKG_test_remote:
CREATE OR REPLACE PACKAGE BODY BJUSTIN.PKG_test_remote IS

FUNCTION t1 RETURN NUMBER
IS
BEGIN
RETURN 1;

END t1;

FUNCTION T2 RETURN NUMBER
IS
BEGIN
RETURN 2;
END;

END PKG_test_remote;

PRC_test_remote_call:
CREATE OR REPLACE PROCEDURE PRC_test_remote_call(nresult OUT NUMBER)
IS
v1 NUMBER;
BEGIN
nresult := bjustin.PKG_test_remote.T1@tt;
END;

Code to call PRC_test_remote_call:
DECLARE

v1 NUMBER;
state_lost EXCEPTION;
PRAGMA EXCEPTION_INIT(state_lost, -04068);
BEGIN
DBMS_OUTPUT.ENABLE;
BEGIN
EXECUTE IMMEDIATE 'ALTER PROCEDURE bjustin.test_remote_call COMPILE';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
PRC_test_remote_call(v1);
EXCEPTION
WHEN state_lost THEN
PRC_test_remote_call(v1);
END;

Tom Kyte

Followup  

May 24, 2004 - 2:39 pm UTC

sorry, but that just isn't going to happen.

that would be like wanting to change the OS binary, while the OS is running, and having everything be OK.

You are making a major change, big time.

Your package has a state

It must be maintained (else that would be a bug, you have some variables in there that must be preserved -- your code MAKES that be so)

If we did not maintain, your program execution would be erroneous. We would reset variables on you silently -- that would *not be good*

If you do not need a state, remove the state from your package, don't use globals, don't have a package state.

Or revert to a simple standalone procedure (no state) and use signature dependency tracking instead of timestamp.

spec compilation

June 10, 2004 - 9:59 am UTC

Reviewer: Ilya from new york

Hi Tom,

just wanted to clarify something: suppose I recompile a spec that has a global variable in it and also recompile all dependent objects. Will that error about invalid package state be received only by the users who were already logged in while the recompile took place or also by the users who will create a new session after the recompile was done?

thanks,
ilya

Tom Kyte

Followup  

June 10, 2004 - 5:00 pm UTC

only those whose session state was destroyed. those who logged in after won't have their session state destroyed.

client-side PL/SQL versus server-side PL/SQL

July 11, 2004 - 12:37 pm UTC

Reviewer: A reader

Hi Tom
what is the difference between client-side PL/SQL versus server-side PL/SQL? Could you give some examples?

thank you and have a wonderful day!:)

Tom Kyte

Followup  

July 11, 2004 - 1:35 pm UTC

client side = forms
server side = stored procedure.



thanx!

July 11, 2004 - 1:42 pm UTC

Reviewer: A reader

So I take it that the forms has a pl/sql engine in it that
is different code (and different functionality) from the server side pl/sql engine? (sorry not familiar with
forms)

Tom Kyte

Followup  

July 12, 2004 - 10:59 am UTC

It is typically lagging a release behind in functionality (there are more features in the server than in forms -- but there is also forms specific features in forms plsql that will never be in the server, since the server does not do things "gui")

ok - thanx!

July 12, 2004 - 11:21 am UTC

Reviewer: A reader


Can dropping an index invalidate a view

September 20, 2004 - 2:39 pm UTC

Reviewer: Logan Palanisamy from Santa Clara, CA USA

Tom,

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/depend.htm#sthref1112

The above Oracle 10g "Concepts Guide" says, dropping an index could invalidate a view on that table. I couldn't belive that. My very simple test also proves that that is not true.

SQL> create table t (f1 varchar2(50), f2 date);

Table created.

SQL> create index t_idx on t(f1);

Index created.

SQL> create or replace view tv as select * from t;

View created.

SQL> select status from user_objects where object_name = 'TV';

STATUS
-------
VALID

SQL> drop index t_idx;

Index dropped.

SQL> select status from user_objects where object_name = 'TV';

STATUS
-------
VALID

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Is the document trying to say something that I don't understand? Or is it something new in Oracle 10g?

Thanks as always. 

Tom Kyte

Followup  

September 20, 2004 - 4:15 pm UTC

ops$tkyte@ORA9IR2> create table t ( x varchar2(5) );
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x) indextype is ctxsys.context;
Index created.
 
ops$tkyte@ORA9IR2> create or replace view v as select * from t where contains(x,'foo') > 0;
View created.
 
ops$tkyte@ORA9IR2> @invalid;
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /
 
no rows selected
 
ops$tkyte@ORA9IR2> drop index t_idx;
 
Index dropped.
 
ops$tkyte@ORA9IR2> @invalid;
ops$tkyte@ORA9IR2> break on object_type skip 1
ops$tkyte@ORA9IR2> column status format a10
ops$tkyte@ORA9IR2> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /
 
OBJECT_TYPE        OBJECT_NAME                    STATUS
------------------ ------------------------------ ----------
VIEW               V                              INVALID



 

ORA-04068 Package State invalid

September 23, 2004 - 4:24 pm UTC

Reviewer: Susan from Watertown, MA

Following up on Jan's question from above, is there something special about packages that will cause this? I read at Metalink that you'll see ORA-04068 error messages when a package has been changed/compiled any existing packages in the shared pool will be flagged as invalid. The next call for the package sees the flag and goes to get a new copy, if the package relied on a package state(i.e. package level variables) you'll get the ORA-04068. Maybe I'm not understanding what "package level variables" but I'm surprised we don't this happening with standalone procedures. Thanks.

Tom Kyte

Followup  

September 24, 2004 - 9:39 am UTC

see above, i believe the issue is the developers or whatever tool they are using are calling dbms_describe.

Unable to drop a view

October 10, 2004 - 10:06 pm UTC

Reviewer: A reader

I am having a huge problem with my 9.2.0.4 database. As far as I can tell, it started after I added a column to a table. The table had some existing FBIs using a packaged function and that package is used very very heavily all over the place. At first, when I tried to add the column, I got a deadlock error where both the blocking/waiting session were my own session (trying to do the alter table add column). So I dropped the FBI and added the column fine. Recreated the FBI. Now there are tons of invalid objects all over the place and they just dont get recompiled. 'alter view/trigger/package' just hangs.

Surprisingly enough, I am unable to even drop a brand new view that I just created and that has no dependents.

create view v as ... works;
drop view v; -- hangs
create or replace view v as -- hangs

While it is hanging, I enabled sql_trace in the session and the trace file keeps doing some selects on obj$, view$, and all the dictionary stuff.

I know this is a case for support, but I just wanted to check if you might have any ideas about it?

Thanks

Tom Kyte

Followup  

October 11, 2004 - 7:41 am UTC

if the trace file is growing -- it by definition is "not hung" -- a hang would be "nothing happening".

Please contact support for this one -- not able to reproduce on my end and not sure how you got into this particular state.

Invalidation of packages

October 15, 2004 - 12:40 pm UTC

Reviewer: Thiru

When a package is modified on the server and reompiled, why does the client who accesses the application get invalidated/discarded packages error? After a couple of trials accessing the package from the application, it is able to get to the revised pkgs. Is it because of the old pkg being cached in memory or some thing like this.

Thanks

Tom Kyte

Followup  

October 15, 2004 - 3:35 pm UTC

no, it is "package STATE has been discarded" -- meaning package has global variables, meaning said variables were set to some value, meaning the said variables were WIPED OUT OF EXISTENCE AND RESET when you recompiled the code.

So, the client must be notified that "hey, the code you were running in this production system -- well guess what, it had a memory but they just lobotomized it -- cut its brain right out and put in a new one.. This new brain, it doesn't have any of the old memory, it is starting over again. Just thought you should know that -- so when your code that counts on that code having a memory acts strange -- you know why"



Basicaly - what would happen to your client application if some subroutine it had run was magically replaced at runtime and its variable values just blanked out, reset. It would be nasty wouldn't it.

that is exactly what you are doing when you compile code into a system that is, well, actually RUNNING that code.


You are the best!!

October 15, 2004 - 3:50 pm UTC

Reviewer: Reader

Awesome explanaton. Crystal clear.

Great!

November 16, 2004 - 5:06 am UTC

Reviewer: Ashim Chakrabarty from India

One more reason to use packages!

Let two procedures run in parallel

March 10, 2005 - 8:56 pm UTC

Reviewer: Sean from NJ, USA

Hi Tom,

I have a procedure p which calls three procedures. The code of p is like this:

Begin
p1,
p2,
p3,
End;

What I want is to let p2 and p3 execute at the same after p1 is done since there is no relationship between p2 and p3, but both p2 and p3 need to run after p1.

Thanks so much for your help.



Tom Kyte

Followup  

March 10, 2005 - 9:05 pm UTC

only way to do it is to use dbms_job but they will run in separate sessions -- separate from the p1 sesssion and separate from eachother.

only applicable if they are in fact separate transactions.

Spontaneous corruption of Stored procedure

March 16, 2005 - 5:38 am UTC

Reviewer: avitos@hotmail.com from Barcelona, Spain

Hello!
i'm maintaining a J2EE application running on Weblogic 8.1 and Oracle 9.2.0.3
However, in cases of heavy traffic in the application, the following extremely weird situation comes up:
Suddently, a synchronization procedure (used to transfer data from development -> public environment) returns an error ORA-00942: table or view does not exist.
The procedure itself appears as valid, but whenever i attempt to recompile it, it is successful and afterwards everything is back to normal.
Do you know any other case where a stored procedure may become spontaneously corrupt? And if so, how can I detect it since the diagnostics return a "valid" status?

Thanks,

Nick


Tom Kyte

Followup  

March 16, 2005 - 8:30 am UTC

does it use dynamic sql? what else is going on? possible that in fact the table does not exist for some period of time but by the time you get back there it is once again there?

look at created and last_ddl_time in the dictionary for all related objects when it happens.

March 17, 2005 - 8:43 am UTC

Reviewer: Cefers.br from Brasil

>>>
You have to do the double touch on a package like that -- think about it.
<<<

Tom, I´m having problems with a PACKAGE. Even after 2nd calling, I still get the error "ORA-04061: existing state of package body "AMESP.ANS" has been invalidated".

The "dobule touch" rule isn´t working at all. I must have done something terribly wrong in my package.

Do you have any ideas about what is wrong?

Obs: I didn´t post the package code here, because it´s pretty large. If you need it, please let me know.

Thanks.

Tom Kyte

Followup  

March 17, 2005 - 9:30 am UTC

show me.

you should be able to replicate this in sqlplus

SQL> exec amesp.ans.procedure....
Success

(now go off and invalidate it)

SQL> exec amesp.ans.procedure....
ORA-04061: existing state of package body "AMESP.ANS

SQL> exec amesp.ans.procedure....
ORA-04061: existing state of package body "AMESP.ANS


show us, cut and paste NO EDITS. 

OK boss, here it goes

March 17, 2005 - 9:57 am UTC

Reviewer: Cefers.br from Brasil

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Mar 17 11:52:41 2005

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Release 9.2.0.5.0 - Production
JServer Release 9.2.0.5.0 - Production

11:52:41 amesp@natsam> select amesp.ans.registro_plano('26','S') from dual;

AMESP.ANS.REGISTRO_PLANO('26','S')
--------------------------------------------------------------------------------
000000000

11:52:47 amesp@natsam> alter table amesp.empresas drop (foo);

Table altered.

11:52:52 amesp@natsam> select amesp.ans.registro_plano('26','S') from dual;
select amesp.ans.registro_plano('26','S') from dual
*
ERROR at line 1:
ORA-04061: existing state of package body "AMESP.ANS" has been invalidated


11:52:55 amesp@natsam> select amesp.ans.registro_plano('26','S') from dual;
select amesp.ans.registro_plano('26','S') from dual
*
ERROR at line 1:
ORA-04061: existing state of package body "AMESP.ANS" has been invalidated


11:52:57 amesp@natsam> select amesp.ans.registro_plano('26','S') from dual;
select amesp.ans.registro_plano('26','S') from dual
*
ERROR at line 1:
ORA-04061: existing state of package body "AMESP.ANS" has been invalidated


11:52:58 amesp@natsam>


Bug #2747350

March 21, 2005 - 9:42 am UTC

Reviewer: Cefers.br from Brasil

Hi, Tom.

I found out that this error occurs due to the bug#2747350 on Oracle9i Release 9.2.0.5.0.

I´ll apply the required patch.

Thanks.

Dropping a package

April 01, 2005 - 10:29 am UTC

Reviewer: Shalu from DC, USA

Tom, this page is really very helpful, it's full of lot of information.

I have one quick question. We can drop a package using:

DROP PACKAGE my_pkg. This will drop both the spec and the body.
DROP PACKAGE BODY my_pkg will drop the body only.

Is it possible to drop a package specification but not the body? Is that possible at all ?

Many Thanks!
Shalu

Tom Kyte

Followup  

April 01, 2005 - 11:18 am UTC

nope, you cannot have a body without a spec (well, you can create the body without the spec, but it'll just be invalid stuff)

packages

April 20, 2005 - 3:26 pm UTC

Reviewer: soji ademeji from lagos, nigeria

i just want to know how to make a package of any product tha can be assessible.
i will be look after to hear from you.

Tom Kyte

Followup  

April 20, 2005 - 9:14 pm UTC

does not compute

performance issue

July 14, 2005 - 7:04 am UTC

Reviewer: Ravi Kumar from Delhi, India

It was really a very useful thread for me.

I have 2 point here.

1. you said if a called procedure is changed then calling procedure will be recompile itself the next time it is run. that means if we use packages we can increase the performance by saving the compile time. But That seems to be only a development phase performance issue.

2. you said if we change the specification of a package then it invalidate dependent objects. Now I can think about a drawback of this.. for example there is package with 20 procedures/functions in it which are called from several places, And I need to add a parameter to one of the procedure. Now because I will change the specification it will invalidate all routines in which this package is called no matter I am calling that changed procedure or not.

Can I have your comments on this please ?

Thanks & Regards
Ravi..

Tom Kyte

Followup  

July 14, 2005 - 11:00 am UTC

1) it breaks the dependency chain in "production" as well, you can update a package body without cascading the invalidations to the entire database. You can patch A PACKAGE BODY without invalidating hundreds of others.

It is most definitely for production this feature, In development everything is a mess and you'll do the specs too. So not so much for there actually.

2) packages packages packages packages packages packages packages

that is my comment. don't even consider little procedures/functions.

group related functions/procs together. The odds are the change of that parameter to that one function DOES impact other code, that is, if they use that package, they likely use that function anyway.

Look at your "real code", do you have a source code file per procedure or do you do it modular like? PLSQL is real code.

Changing a synonym causes a package to invalidate.....

September 11, 2005 - 3:10 am UTC

Reviewer: Prashant from San Jose, CA

Hi Tom,

I have 3 schemas
Schema A - This has one table Table X, granted to Schema C
Schema B - This has one table Table X, granted to Schema C
Schema C - This has one synonym Synonym Z

The synonym Z switches between Table X in schema A to Table X in schema Y whenever refresh is taking place to have 24*7 availability, using the below command.

create or replace synonym Z for A.X;

create or replace synonym Z for B.X;

In Schema C, there is also a package that accesses the synonym Z in Schema C.
This package invalidates whenever the create or replace synonym command runs?

Tom, I know that the first time the package is accessed it will recompile and the subsequent accesses will be fine. But I want to make sure that I don't get the error even for the first time. Is there a way?

Regards
Prashant

Tom Kyte

Followup  

September 11, 2005 - 8:40 am UTC

10g - repointing the synonym will no longer invalidate the package body.


Until then, dynamic sql, your parses go up (since in 9i, execute immedate and open cursor for ... parse every time - in 10g, execute immediate doesn't have to parse every time), you lose the dependency mechanism, ...

September 11, 2005 - 9:33 am UTC

Reviewer: Alberto Dell'Era from Milan, Italy

> your parses go up

Unless you use dbms_sql to emulate the PL/SQL cursor cache (you did illustrate the technique it somewhere, here's the test case for a synonym repoint) (9.2.0.6):

dellera@ORACLE9I> create table t1 as select * from dual;

Table created.

dellera@ORACLE9I> create table t2 as select * from dual;

Table created.

dellera@ORACLE9I> create or replace synonym my_dual for t1;

Synonym created.

dellera@ORACLE9I> create or replace package test is
2 procedure test;
3 end test;
4 /

Package created.

dellera@ORACLE9I> create or replace package body test is
2 g_cursor integer;
3
4 procedure test
5 is
6 l_count int;
7 l_int int := 1;
8 l_ignore int;
9 begin
10 if g_cursor is null then
11 g_cursor := dbms_sql.open_cursor;
12 dbms_sql.parse (g_cursor, 'select count(*) from my_dual where :x = 1', dbms_sql.native);
13 dbms_sql.define_column (g_cursor, 1, l_count);
14 end if;
15
16 dbms_sql.bind_variable (g_cursor, ':x', l_int);
17 l_ignore := dbms_sql.execute_and_fetch (g_cursor, exact => true);
18 dbms_sql.column_value (g_cursor, 1, l_count);
19 dbms_output.put_line (l_count);
20 end test;
21
22
23 /* -- this throws "ORA-04068: existing state of packages has been discarded"
24 procedure test
25 is
26 l_count int;
27 l_int int := 1;
28 begin
29 select count(*) into l_count from my_dual where l_int = 1;
30 dbms_output.put_line (l_count);
31 end test;
32 */
33
34 end test;
35 /

Package body created.

dellera@ORACLE9I> exec test.test;
1

PL/SQL procedure successfully completed.

dellera@ORACLE9I> create or replace synonym my_dual for t2;

Synonym created.

dellera@ORACLE9I> select object_type, object_name, status from user_objects where object_name = 'TEST';

OBJECT_TYPE OBJECT_NAME STATUS
-------------------- ------------------------------ ---------------------
PACKAGE TEST VALID
PACKAGE BODY TEST VALID

dellera@ORACLE9I> exec test.test;
1

PL/SQL procedure successfully completed.

Obviously, g_cursor is not closed when the # of opened cursors approaches session_cached_cursors (open_cursors in 9.2.0.4 and before) so one should (perhaps) increase the parameter a bit.

In real code, one would also want to close g_cursor if an exception occurs during the cursor manipulation, and reset it to null.

Tom Kyte

Followup  

September 11, 2005 - 10:26 am UTC

yes, this is true, thought about mentioning it, but the code complexity goes up too....

Thanks a lot.....but

September 11, 2005 - 6:06 pm UTC

Reviewer: Prashant from San Jose, CA

Hi Tom and Alberto (hope I got it right),

The solution given is great, but I think it would involve some effort since we have used these synonyms liberally :) in our application and its already in production.

I need a quick fix, may or may not be elegant, whenever I create the synonym I will compile the invalid packages as well.

Would it work?

(Will remember for future developments unless I am using 10g)

Thanks
Prashant

Tom Kyte

Followup  

September 11, 2005 - 7:00 pm UTC

would what work??!?!

dynamic sql - yes
10g - yes

those where the two ideas put forth.

Sorry wasn't clear...will force compile whenever the synonym is recreated

September 11, 2005 - 8:57 pm UTC

Reviewer: Prashant from San Jose, CA

Hi Tom,

I said that as a temporary workaround => Whenever I am recreating the synonyms I will compile the invalidated packages in the same script, so that I don't see the ORA-04068.

I figured out that, its a silly question, sorry for wasting your time.

Thanks
Prashant

Tom Kyte

Followup  

September 12, 2005 - 7:19 am UTC

that will not really "fix it", if it appeared to have done so, it was a "fluke".  A red herring, a coincidence.


ops$tkyte@ORA9IR2> create or replace package stateful
  2  as
  3          procedure p;
  4          procedure p2;
  5  end;
  6  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace synonym xx for sys.dual;
 
Synonym created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body stateful
  2  as
  3          g_cnt number := 0;
  4
  5  procedure p
  6  is
  7  begin
  8          g_cnt := g_cnt+1;
  9          for x in ( select * from xx ) loop null; end loop;
 10  end;
 11
 12  procedure p2
 13  is
 14  begin
 15          dbms_output.put_line( g_cnt );
 16  end;
 17
 18  end;
 19  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec stateful.p2;
0
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec stateful.p; stateful.p; stateful.p;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec stateful.p2;
3
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> pause
<b>at this point, in another session, I did this:

ops$tkyte@ORA9IR2> create or replace synonym xx for sys.all_users;
Synonym created.

ops$tkyte@ORA9IR2> alter package "STATEFUL" compile body;
Package body altered.

then back to original session:</b>
 
ops$tkyte@ORA9IR2> exec stateful.p2;
BEGIN stateful.p2; END;
 
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OPS$TKYTE.STATEFUL" has been
invalidated
ORA-04065: not executed, altered or dropped package body "OPS$TKYTE.STATEFUL"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
 
 
ops$tkyte@ORA9IR2> exec stateful.p2;
 
PL/SQL procedure successfully completed.
 


That is not a "fix" 

Back to encapsulation (sorry)

September 13, 2005 - 12:37 pm UTC

Reviewer: Aaron from Colorado, USA

Tom - earlier in this thread you gave an example of "privatizing" procedures and functions within packages. I would like to do the same with cursors. I operate on 9.2.0.6 on AIX.

an example of what I am trying to do is:

CREATE OR REPLACE PACKAGE BODY PRIVATE_TEST AS

CURSOR curTEST(p_ARG);

CURSOR curTEST(P_ARG)
IS
SELECT P_ARG FROM DUAL;

END PRIVATE_TEST;

Will this method work? I like the idea of "strogly typed" cursors, but would like to be able to reuse them without exposing them to the world.

Thanks!




Tom Kyte

Followup  

September 13, 2005 - 1:11 pm UTC

yes, you can hide cursors in the package body and access them, in fact, you can go further and hide them in a procedure in a package body.

And your procedure may return a ref cursor to "expose" them..

Thanks Tom

September 13, 2005 - 1:55 pm UTC

Reviewer: Aaron from CO

Tom, thanks for the quick reply! I ended up figuring it out by creating a test package. I provided the code below so that others might be able to utilize this "good way of doing things". . .

CREATE TABLE TEST_TABLE
(
TEST_DATE DATE NOT NULL
);

CREATE OR REPLACE PACKAGE TRY_OUT AS
PROCEDURE PUBLIC_SPROC;
END TRY_OUT;
/

CREATE OR REPLACE PACKAGE BODY TRY_OUT AS

CURSOR PRIVATE_CURSOR(P_DATE DATE)
IS
SELECT P_DATE "PARAM_DATE"
FROM DUAL;

PROCEDURE PUBLIC_SPROC
IS
recTest PRIVATE_CURSOR%ROWTYPE;
BEGIN
FOR recTest IN PRIVATE_CURSOR(TRUNC(SYSDATE)) LOOP
INSERT INTO TEST_TABLE VALUES(recTest.PARAM_DATE);
END LOOP;
END PUBLIC_SPROC;
END TRY_OUT;
/

Tom Kyte

Followup  

September 13, 2005 - 4:13 pm UTC

(well hopefully you don't have too much code that looks like that - that should be an insert into select .... -- no code!)

Cursor encapsulation

October 17, 2005 - 2:19 pm UTC

Reviewer: Aaron from Colorado

Tom,
Thanks for the follow up - I appologize for the poor example. To my point:

Earlier you spoke of returning a ref cursor with a procedure that calls the private cursor. I have spent a good deal of time trying different methods and scouring your site, but for the life of me cannot figure out how to do it. Could you please help me out with an example?

I have a Package that declares a Type of ref cursor that I would like the procedure to return. Below is an example of the broken code:

CREATE OR REPLACE PACKAGE CURSOR_TYPES AS
TYPE curType IS REF CURSOR;
END CURSOR_TYPES;
/

CREATE OR REPLACE PACKAGE CLIENT_TEST AS
PROCEDURE ODS_GET_PROCESSES RETURN Cursor_Types.CURTYPE;
END CLIENT_TEST;
/

CREATE OR REPLACE PACKAGE BODY CLIENT_TEST AS

CURSOR C_PROCESS_ID (p_ProcessId NUMBER)
IS
SELECT *
FROM EPRO_PROCESS
WHERE PROCESS_ID = p_ProcessId;

PROCEDURE ODS_GET_PROCESSES(cProcesses OUT Cursor_Types.CURTYPE)
IS
l_proc Cursor_Types.CURTYPE;

BEGIN
OPEN C_PROCESS_ID(50001);
--this is my confusion point
l_proc := C_PROCESS_ID(50001);
CLOSE C_PROCESS_ID;
RETURN l_proc;
END;

END CLIENT_TEST;
/

Tom Kyte

Followup  

October 17, 2005 - 2:45 pm UTC

it would be simply:


.......
is
begin
open c_process_id for
select * from epro_process where process_id = p_process_id;
end;

end client_test;
/


</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

reword question

October 17, 2005 - 3:05 pm UTC

Reviewer: Aaron

Thanks for the prompt reply!

I should have worded the problem differently: The reason I want to explicitly declare the cursor is so that I can reuse it in other pleaces within the package - this is only one place where the cusor would be returned to the requestor.

I understand creating the implicit cursor within the procedure, that is our standard practice for returning data to the client. This case a bit different though - I would like to keep the cursor definition in one place so that it is easier to maintain.

The "English" version of what I would like to do is:
create a procedure that returns a REF CURSOR that is filled using a different, private CURSOR, all wrapped up in a package.

Sorry if I am being unclear . . .


Tom Kyte

Followup  

October 17, 2005 - 8:56 pm UTC

that syntax is not supported....

sorry - it just doesn't work that way.

Thanks

October 18, 2005 - 10:10 am UTC

Reviewer: Aaron

Well, Thanks for the effort anyhow . . .

recompiling just the package body in production

November 17, 2005 - 1:39 pm UTC

Reviewer: Menon

"You need not "reconnect", you need to catch the error and retry the operation
AFTER acknowledging the fact that your state was wiped out. That data
inconsistency problem you mention is EXACTLY the reason the error is raised! We
give you the opportunity to deal with it (or not -- upto you)."
Looks like, if we just change the package body of a package and recompile it at production one (poor) alternative is to bring all the apps that are connected to the database. Another alternative could be to retry once more at the place where you invoke this package. Is this a "normal" practice? Because this could reduce the downtime a lot if we only change the body. If this is true, I would suspect that this has to be done at a top level - either at the client which is invoking a procedure or all the top level (public) procedure(s) that could retry their logic twice on catching this error.
So do people use this technique and is it a valid one?

Tom Kyte

Followup  

November 18, 2005 - 9:52 am UTC

It is not normal to compile code that is being used.

That is not normal.





sure..

November 18, 2005 - 10:32 am UTC

Reviewer: Menon

"It is not normal to compile code that is being used."

I understand that...The code changes only after a run of it has been completed...The requirement is to use the new version of the code for the next run without downtime...And since Oracle compiles automatically the second time, what would be the harm in doing so?

Thanx!

Tom Kyte

Followup  

November 18, 2005 - 3:39 pm UTC

I would ask then - why does the package have a state IF the state isn't necessary, if the state of the package (global variables) can be wiped out - one might ask "why is it there"

because if it need not be there, then get rid of it and you'll find that the package can be recompiled without getting the "existing state of packages discarded"

makes sense

November 18, 2005 - 5:06 pm UTC

Reviewer: Menon

but this happens even if the state is a global constant?

Tom Kyte

Followup  

November 19, 2005 - 9:51 am UTC

yes, but they could be in a globals package and if you change a global, well, then you changed everything and you would in fact need a restart.

Help

November 21, 2005 - 8:43 am UTC

Reviewer: A reader

Hi Tom,
How to emulate the command

"SQL> create or replace TABLE "

 

Tom Kyte

Followup  

November 21, 2005 - 8:54 am UTC

give me the possible use case?

yes

November 21, 2005 - 9:50 am UTC

Reviewer: A reader

Hi Tom,
My requirement is"
I am writing a procedure which must drop a table if it
already exists or it must create it if it does not exist"



Tom Kyte

Followup  

November 21, 2005 - 9:58 am UTC

that doesn't make sense.... 


if table_exists
then
   drop it
else
   create it
end if?


but anyway, using exception blocks you can handle any error, if it is to drop it if it exists - just "drop it", but wrap the execute immediate call in an exception block and do nothing if the drop fails due to "table does not exist"

ops$tkyte@ORA10GR2> create or replace procedure drop_table( p_tname in varchar2 )
  2  as
  3          table_does_not_exist exception;
  4          pragma exception_init( table_does_not_exist, -942 );
  5  begin
  6          /* ..... */
  7          begin
  8                  execute immediate 'drop table ' || p_tname;
  9          exception
 10                  when table_does_not_exist
 11                  then
 12                          dbms_output.put_line( 'table doesn''t exist' );
 13          end;
 14  end;
 15  /
 
Procedure created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA10GR2> exec drop_table('t');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> exec drop_table('t');
table doesn't exist
 
PL/SQL procedure successfully completed.
 

Thanks

November 21, 2005 - 12:41 pm UTC

Reviewer: A reader

Hi Tom,
Thanks for your response.
I tried this one.

SQL> create or replace procedure proc
  2  as
  3  table_doesnot_exist exception;
  4  pragma exception_init(table_doesnot_exist,-00942);
  5  begin
  6  execute immediate 'drop table tab1 cascade constraints';
  7  execute immediate 'create table tab1(x int,y varchar2(10))'; -- Need this stmt. to be 
  8  exception                                                    -- even though exception is                                                                       --raised
  9  when table_doesnot_exist then
 10   dbms_output.put_line('Table doesnot exist');
 11  end;
 12* /

Procedure created.

SQL> set serverout on

SQL> exec proc
Table doesnot exist

PL/SQL procedure successfully completed.

SQL> desc tab1
ERROR:
ORA-04043: object tab1 does not exist


I want the " create table statement to be executed".
How to achieve that??
Does it need to be enclosed in another block??
 

Tom Kyte

Followup  

November 21, 2005 - 12:50 pm UTC

take a closer look at my example, see how the execute immediate is wrapped in it's own begin/end block with an exception handler?

to A reader

November 21, 2005 - 2:08 pm UTC

Reviewer: Marcio Portes from Brazil

How about this? (From Tom's example of course)


create or replace procedure drop_table( p_tname in varchar2
)
as
table_does_not_exist exception;
pragma exception_init( table_does_not_exist, -942 );
begin
begin
execute immediate 'drop table ' || p_tname;
exception
when table_does_not_exist then
dbms_output.put_line( 'table doesn''t exist, creating ...' );
execute immediate 'create table tab1(x int,y varchar2(10))';
end;
end;
/


Tom Kyte

Followup  

November 21, 2005 - 2:24 pm UTC

I think they want to

a) drop table if it exists
b) then ALWAYS create the table


so the execute immediate 'create table' would go right below the END; statement it is right above.

to a reader

November 21, 2005 - 2:31 pm UTC

Reviewer: Marcio Portes

And remember to have create table granted to you directly otherwise you're going to come up with this error:

ops$mportes@FCCUAT9I> exec drop_table('T')
table doesn't exist, creating ...
BEGIN drop_table('T'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "OPS$MPORTES.DROP_TABLE", line 12
ORA-00942: table or view does not exist
ORA-06512: at line 1

Regards,

Sorry

November 21, 2005 - 2:45 pm UTC

Reviewer: A reader

Tom you right I didn't read the requirement in the first place. Allow me to re-code the proc. It will look like this.

create or replace procedure drop_table( p_tname in varchar2
)
as
table_does_not_exist exception;
pragma exception_init( table_does_not_exist, -942 );
begin
begin
execute immediate 'drop table ' || p_tname;
exception
when table_does_not_exist then
dbms_output.put_line( 'table doesn''t exist, creating ...' );
end;
execute immediate 'create table tab1(x int,y varchar2(10))';

end;
/

ORA-04068

November 28, 2005 - 10:05 pm UTC

Reviewer: A reader

Tom,

I have a package which has many procedures, two of them are having compilation errors.

When i try to execute the good  stored procedure but it still fails with ORA-04068: existing state of packages has been discarded.

Does this mean that if one procedure in a package goes invalid due to "compilation errors", the remaining procedures as well which inturn caused the package unusable. Ofcourse, we will have to fix the compilation errors.

This is what my test shows :
SQL> exec DBOR_NC3_DATALOAD_PKG.GATHER_STATS_ON_TAB_1
BEGIN DBOR_NC3_DATALOAD_PKG.GATHER_STATS_ON_TAB_1; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "IOMDBO.DBOR_NC3_DATALOAD_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1


SQL> desc dbor_nc3_dataload_pkg
PROCEDURE ALTER_INDEX_REBUILD_1
PROCEDURE ALTER_INDEX_REBUILD_2
PROCEDURE ALTER_INDEX_UNUSABLE_1
PROCEDURE ALTER_INDEX_UNUSABLE_2
PROCEDURE GATHER_STATS_ON_TAB_1
PROCEDURE GATHER_STATS_ON_TAB_2
PROCEDURE GET_CURR_DBOR_NC3_TABDET
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_CURR_TABLE_CURSOR            REF CURSOR              IN/OUT
 P_CURR_TABLE_SUFFIX            VARCHAR2                OUT
PROCEDURE GET_DBOR_NC3_TAB_CNT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_CURR_TABCNT_CURSOR           REF CURSOR              IN/OUT
PROCEDURE REPOINT_SYNONYM_AND_GRANT_1
PROCEDURE REPOINT_SYNONYM_AND_GRANT_2
PROCEDURE TRUNCATE_DBOR_NC3_TAB_1
PROCEDURE TRUNCATE_DBOR_NC3_TAB_2

 

Tom Kyte

Followup  

November 28, 2005 - 11:50 pm UTC

you have code that is needed to be valid that is not valid - the package cannot 'fix' itself. you'll need to fix the broken package.

Confused....pls clarify..ORA-04068

December 02, 2005 - 10:10 am UTC

Reviewer: Subhasis Gangopadhyay from INDIA

Hi Tom,

For a few years I am reviewing you great answers and those are most useful to me in every sense.Now recently, I got confused over ORA-4068 issue in a specific scenario.

First I am putting the scenario below which I have simulated but exactly this scenario is making our life too difficult in live DW env......

create table subha(a number);
 
  1  create or replace procedure proc_subha
  2  is
  3  cnt number;
  4  begin
  5  proc_subha1;
  6  select count(*) into cnt from subha;
  7* end;
  8  /
 
Procedure created.
 
SQL> create or replace procedure proc_subha1
  2  is
  3  str varchar2(1000):='alter table subha modify (a varchar2(10))';
  4  begin
  5  execute immediate str;
  6  end;
  7  /
 
Procedure created.
 
SQL> begin
  2  proc_subha1;
  3  proc_subha;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04064: not executed, invalidated procedure "YMDBAADM.PROC_SUBHA"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 3


SQL> select object_name,status from dba_objects
  2  where object_name LIKE '%SUBHA%';

OBJECT_NAME        STATUS
-------------------------------------------PROC_SUBHA          INVALID
PROC_SUBHA1         VALID

Now my question is, why the second procedure(proc_subha) called from the anonymous block can not recompile itself
which is done when I call the two procedures seperately(and which is normal).Is these any memory reference is done when the block is invoked which get's invalidated when the first ddl firing procedure(proc_subha1) is run.And if so then it should get recompiled itself.I understand that if I call the two procedure within a PACKAGE or if the first ddl procedure is called within the body of second dependent procedure then there is no problem.

But when I do the below thing,then everything is fine.

  1  create or replace procedure proc_subha
  2  is
  3  cnt number;
  4  begin
  5  proc_subha1;--DDL is fired from here as created earlier
  6  select count(*) into cnt from subha;
  7* end;
  8  /
 
Procedure created.
 
SQL> exec proc_subha
 
PL/SQL procedure successfully completed.

Another interesting thing I am mentioning here is that, when I try to compile proc_subha within proc_subha1 just before exiting, then also same error is thrown.Though in this case the procedure 'proc_subha' is valid in dba_object unlike previous scenario.

  1  create or replace procedure proc_subha1
  2  is
  3  str varchar2(1000):='alter table subha modify (a varchar2(10))';
  4  begin
  5  execute immediate str;
  6  str:='alter procedure proc_subha compile';
  7  execute immediate str;
  8* end;
SQL> /

Procedure created.

  1  create or replace procedure proc_subha
  2  is
  3  cnt number;
  4  begin
  5   select count(*) into cnt from SUBHA;
  6* end;
SQL> /

Procedure created.

SQL> BEGIN
  2  proc_subha1;
  3  proc_subha;
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "YMDBAADM.PROC_SUBHA"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 3


SQL> select object_name,status from dba_objects
  2  where object_name LIKE '%SUBHA%';

OBJECT_NAME   STATUS                                        
-----------------------------------------

PROC_SUBHA     VALID
PROC_SUBHA1    VALID

Please clarify why this is happening.Is there any king of in memory reference while invoking the anonymous block and that's why recompilation in data dictionary does not make any difference.But what I know is taht the compiling the the code is loaded into memory.Is this something related to PGA.

Many thanks
& Regards

Subhasis
 

Tom Kyte

Followup  

December 02, 2005 - 11:21 am UTC

you have code running.

running code is dependent on table T.

code that is running runs other code that modified T.

bamm. game over. I don't see how you can expect this to work? code that is running cannot compile itself.

Confused....pls clarify..ORA-04068

December 02, 2005 - 12:08 pm UTC

Reviewer: deba from UK

In reference to the above thread created by Subhasis Gangopadhyay with
subject "Confused....pls clarify..ORA-04068 ", I would like to know
few things.
You are saying "code that is running cannot compile itself.". But if you
see the second case, there are two things. First one is PROC_SUBHA1 is
compiling PROC_SUBHA, not PROC_SUBHA1. Socond thing is , due to dynamic
recompiling, status of PROC_SUBHA is "VALID". But according to you, the
status of PROC_SUBHA should have been "INVALID" ( as you can see in the
first case of above thread ) due to PROC_SUBHA1. So PROC_SUBHA should have
been executed. How can you explain this thing ?

Thanks
Deba

Tom Kyte

Followup  

December 02, 2005 - 12:20 pm UTC

there is an anonymous block that calls subha1, subha.

This is compiled code.

There is subha1 and subha, they are compiled code.

Program calls subha1, subha1 invalidates subha - the anonymous block (which is compiled running code) goes to run subha but says "hey, no fair, it went away, but I cannot recompile myself on the fly in order to compile it on the fly..."


you have running code that is trashed. i don't know what else to expect here other than "it won't work"

Excellent Work once again!!

December 06, 2005 - 2:20 pm UTC

Reviewer: OWB from New York, NY

Thanks Tom for your wonderful insights!

February 21, 2006 - 1:46 pm UTC

Reviewer: Su Baba from Ca, USA

Back to the original question regarding the dependency chain of the procedure a and b. Is this no longer valid on Oracle 10g or even 9i?

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE OR REPLACE PROCEDURE b AS
  2  BEGIN
  3     null;
  4  END;
  5  /

Procedure created.

SQL> 
SQL> CREATE OR REPLACE PROCEDURE a AS
  2  BEGIN
  3     b;
  4  END;
  5  /

Procedure created.

SQL> select object_type, object_name, status
  2  from   user_objects
  3  where  status = 'INVALID'
  4  order  by object_type, object_name
  5  /

no rows selected

SQL> CREATE OR REPLACE PROCEDURE b AS
  2  BEGIN
  3     null;
  4  END;
  5  /

Procedure created.

SQL> select object_type, object_name, status
  2  from   user_objects
  3  where  status = 'INVALID'
  4  order  by object_type, object_name
  5  /

no rows selected


So do you still recommend the use of packages over standalone procedures in this sense?
 

Tom Kyte

Followup  

February 22, 2006 - 8:10 am UTC

New optimization.  IF the code isn't modified AT ALL, not a byte is different, it recognizes that and does not actually recompile it.

Lets just change the case of the word NULL; in b


ops$tkyte@ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

ops$tkyte@ORA10GR2> CREATE OR REPLACE PROCEDURE b AS
  2  BEGIN
  3     null;
  4  END;
  5  /

Procedure created.

ops$tkyte@ORA10GR2> CREATE OR REPLACE PROCEDURE a AS
  2  BEGIN
  3     b;
  4  END;
  5  /

Procedure created.

ops$tkyte@ORA10GR2> select object_type, object_name, status
  2  from   user_objects
  3  where  status = 'INVALID'
  4  order  by object_type, object_name
  5  /

no rows selected

ops$tkyte@ORA10GR2> CREATE OR REPLACE PROCEDURE b AS
  2  BEGIN
  3     NULL;
  4  END;
  5  /

Procedure created.

ops$tkyte@ORA10GR2> select object_type, object_name, status
  2  from   user_objects
  3  where  status = 'INVALID'
  4  order  by object_type, object_name
  5  /

OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------- ------------------------------ -------
PROCEDURE           A                              INVALID

 

April 07, 2006 - 7:44 pm UTC

Reviewer: A reader from USA

Hi,

Working in 9i Database.

Does deleting the partition can invalidate the package too?

Following is my scenario:

Package A --
Containd Proc A
Proc B

Proc A -- creates a partition for a table 'alter table <table> add partition p_||id'.

Proc B -- drops the partition using 'alter table <table> drop partition p_||id;

Package B -- calls PackageA.Proc B
When executed, it errros out with the error that Package A is in invalid state.

Any clue as to how can this issue be resolved.




Tom Kyte

Followup  

April 08, 2006 - 9:49 am UTC

yes, that'll invalidate the package since the package is dependent on the TABLE (not the partitions therein) and the table has been structurally modified to a degree that necessitates recompilation.

You'd have to give a real "for example" - AS LITTLE CODE AS POSSIBLE (eg: a test case).


Unless package A statically references the table, I don't see any issues - but you don't have a full example there.

April 08, 2006 - 2:38 pm UTC

Reviewer: A reader

Thanks Tom.

Two things:

1. This code works fine in 10g DB. Is there any new feature that was added to 10g that doesn't make the package A invalid.

2. What do you mean by "static reference". Do you mean that if in package A, somewhere, if we are referring to the specific partition, then that is static reference.

Thanks in advance.

Tom Kyte

Followup  

April 08, 2006 - 3:00 pm UTC

one thing from you

a) test case


give me an example to work with here please.



by static reference I mean you have some static SQL that refers to the TABLE itself (forget about the partition, the TABLE)

April 10, 2006 - 1:53 pm UTC

Reviewer: A reader

Following is the sample code:

Table script:

create table TableA
(
REQUEST_ID NUMBER
) partition by list(request_id) (partition p1 values (1) ) ';

Package A

CREATE OR REPLACE PACKAGE PackageA AS

PROCEDURE process_request( p_request_id NUMBER
);

PROCEDURE move_request( p_request_id NUMBER
) ;
END PackageA ;

CREATE OR REPLACE PACKAGE BODY PackageA AS

PROCEDURE process_request( p_request_id NUMBER) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines ADD PARTITION p_'|| p_request_id || ' VALUES ('||p_request_id || ')';
INSERT INTO TableA values (p_request_id);
COMMIT;
END;

PROCEDURE move_request ( p_request_id IN NUMBER) IS
BEGIN
INSERT INTO TABLEB SELECT * FROM TABLEA
WHERE request_id = p_request_id;
COMMIT;
EXECUTE IMMEDIATE 'ALTER TABLE jtf_fm_int_request_lines DROP PARTITION p_'||p_request_id;
COMMIT;
END;

The packageA.move_request is the procedure that is being called by PackageB, which errors out.

My question is this -- why is this code working fine in 10g but failing in 9i.

Thank you in advance.

Tom Kyte

Followup  

April 11, 2006 - 11:01 am UTC

incremental refinements happens all of the time in Oracle - there are fewer invalidations in 10g than there were in 9i than there were in 8i than there were.......


I still don't see a "errors out" situation here - are we missing something, perhaps package b for example and the steps to reproduce.


(too many commits in there... )

April 11, 2006 - 12:44 pm UTC

Reviewer: A reader

thanks Tom.

These procedures are executed thru Java Code. Java calls PackageB.ProcA, which performs other operations and calls PackageA.ProcB.

As soon as PackageA.ProcB is executed, the state the package becomes invalid and I start getting Java SqlException

java.lang.RuntimeException: java.sql.SQLException: ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PackageA" has been invalidated
ORA-04065: not executed, altered or dropped package "PackageA"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1


Tom Kyte

Followup  

April 11, 2006 - 6:11 pm UTC

yup, just means in 9i the invalidated the code and 10g has an optimization that does not invalidate it.

recompile

April 18, 2006 - 1:39 pm UTC

Reviewer: sat from usa

is there a fastest way to recompile the objects other than alter <object type > <object nAme > compile in each version

Tom Kyte

Followup  

April 18, 2006 - 3:36 pm UTC

just let them fix themselves? I do.

Recover from partition add/drop

June 09, 2006 - 10:28 am UTC

Reviewer: Randall from STL

We have a 3rd party logging app that can only call procedures.  It logs millions of rows a day into a partitioned table.  Issue is every time a table partition is drop/added procedures go invalid and the data for that call is lost as it errors out.  

So what I have done is created a simple package to write the data to the table and have the procedure call the package.

Here is what it looks like:

CREATE OR REPLACE PROCEDURE sp_write_tag (
   input1    IN       VARCHAR2,
   input2    IN       VARCHAR2,
   output2   OUT      VARCHAR2)
IS
BEGIN
   output2 := input2;
   BEGIN
      -- Log Data
      pkg_tag_hist.log_tag (input1, input2, output2);
   EXCEPTION
      WHEN OTHERS THEN
         p('Try Again');
          -- It it fails for some reason, try again
         pkg_tag_hist.log_tag (input1, input2, output2);
   END;

   COMMIT;
END;
/

CREATE OR REPLACE PACKAGE pkg_tag_hist
AS
   PROCEDURE log_tag (
      input1    IN       VARCHAR2,
      input2    IN       VARCHAR2,
      output2   IN      VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_tag_hist
IS
   PROCEDURE log_tag (
      input1    IN   VARCHAR2,
      input2    IN   VARCHAR2,
      output2   IN   VARCHAR2)
   IS
   BEGIN
      INSERT INTO rspk_trend.tag_value_hist
           VALUES (SYSTIMESTAMP, input1, input2, output2);
    END;
END;
/

Now this works fine, except once an error forces the procedure to the "second try", it never goes back until the client reconnects.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> connect rspk_rw@FLEXDEV
Enter password: *******
Connected.

SQL> set Serveroutput ON
SQL> DECLARE
  2     input1    VARCHAR2 (200);
  3     input2    VARCHAR2 (200);
  4     output2   VARCHAR2 (200);
  5  BEGIN
  6     input1 := 'Test1';
  7     input2 := '99';
  8     output2 := NULL;
  9     rspk.sp_write_tag (input1, input2, output2);
 10     DBMS_OUTPUT.put_line ('OUTPUT2 = ' || output2);
 11     COMMIT;
 12  END;
 13  /
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> -- Alter logging table here
SQL> 
SQL> /
Try Again
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Try Again
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Try Again
OUTPUT2 = 99

PL/SQL procedure successfully completed.

So after the logging table is altered, the procedure always fails the first try.  

Why is this and is there a better way?

Thanks..



SQL>  

Tom Kyte

Followup  

June 09, 2006 - 1:13 pm UTC

"all data for that call is lost"

explain.

without a full example, I cannot begin to look...

you are entirely hiding the error message as well.

June 09, 2006 - 2:34 pm UTC

Reviewer: Randall from STL

"all data for that call is lost"

The app is not smart enough to catch the       error so the data it is trying to log fails to be logged.  It goes into an application log be does not do the users any good if it doesn't get logged in the database.  

I modified the base proc to provide more output.
CREATE OR REPLACE PROCEDURE sp_write_tag (
   input1    IN       VARCHAR2,
   input2    IN       VARCHAR2,
   output2   OUT      VARCHAR2)
IS
BEGIN
   output2 := input2;

   BEGIN
      -- Log Data
      p ('Trying to Log First Time');
      pkg_tag_hist.log_tag (input1, input2, output2);
      p ('Data Logged First Time SUCCESS');
   EXCEPTION
      WHEN OTHERS THEN
         p ('Data Logged First Time ERROR = ' || SQLERRM);
         p ('Trying to Log Second Time');
         -- It it fails for some reason, try again
         pkg_tag_hist.log_tag (input1, input2, output2);
         p ('Data Logged Second Time SUCCESS');
   END;

   COMMIT;
END;
/

-- Here is the results
SQL> SET Serveroutput ON
SQL> 
SQL> DECLARE
  2     input1    VARCHAR2 (200);
  3     input2    VARCHAR2 (200);
  4     output2   VARCHAR2 (200);
  5  BEGIN
  6     input1 := 'Test1';
  7     input2 := '99';
  8     output2 := NULL;
  9     rspk.sp_write_tag (input1, input2, output2);
 10     DBMS_OUTPUT.put_line ('OUTPUT2 = ' || output2);
 11     COMMIT;
 12  END;
 13  /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL>  -- Alter logging table here
SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

 

Tom Kyte

Followup  

June 09, 2006 - 5:39 pm UTC

you are missing a complete, yet concise, example.

I don't know why your log package would lose anything.

make it small, make it concise, make it self explanatory (eg: see, you see THIS output and we should have seen THIS output instead so we lost X)

June 12, 2006 - 8:43 am UTC

Reviewer: Randall from STL

Sorry if you misunderstood my question.  Let me try again.

Prior to this coding change, the sp_write_tag procedure would write directly to the partitioned table, so the first call after the table was modified would error out.  The application does not handle this so that data point was lost.

The way it is coded right now, we don't lose anything.  By catching the error in the base procedure, and retrying to execute the pkg_tag_hist.log_tag, We do log all data.

What we are now seeing is that prior to the table alter, the data is logged on the first try.

------------------------------
SQL> /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.
------------------------------

This is what you would expect.

Then the table is altered, we see:

------------------------------
SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.
------------------------------
This also is expected,  the package body for pkg_tag_hist has been invalidated because of the alter table.  So when the procedure tries to log the data, it gets the error "ORA-06508" the first time.  We catch that and try to run it again.  Second time the package has been reloaded and logs the data and returns success.

What we don't expect to see is that every call there after ALSO errors on the first try, then succeeds on the second.

-------------------------------

SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.
--------------------------------------------

Which brings me to my question. 

Why does the call to pkg_tag_hist.log_tag continue to fail on the first try even after the package is reloaded?

------------------------------------------------------------

CREATE TABLE TAG_VALUE_HIST
(
  ACTION_DT  TIMESTAMP(3)                       NOT NULL,
  TAG_NAME   VARCHAR2(256)                      NOT NULL,
  IN_VALUE   VARCHAR2(256)                          NULL,
  OUT_VALUE  VARCHAR2(256)                          NULL
)
PARTITION BY RANGE (ACTION_DT) 
(  
  PARTITION TAG_2006_P0053 VALUES LESS THAN (TIMESTAMP'2006-06-10 18:00:00'),  
  PARTITION TAG_2006_P0054 VALUES LESS THAN (TIMESTAMP'2006-06-15 00:00:00'),  
  PARTITION TAG_2006_P0055 VALUES LESS THAN (TIMESTAMP'2006-06-20 06:00:00')
)
NOCACHE
/


CREATE OR REPLACE PACKAGE pkg_tag_hist
AS
   PROCEDURE log_tag (
      input1    IN   VARCHAR2,
      input2    IN   VARCHAR2,
      output2   IN   VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_tag_hist
IS
   PROCEDURE log_tag (
      input1    IN   VARCHAR2,
      input2    IN   VARCHAR2,
      output2   IN   VARCHAR2)
   IS
   BEGIN
      INSERT INTO tag_value_hist
           VALUES (SYSTIMESTAMP, input1, input2, output2);
   END;
END;
/

CREATE OR REPLACE PROCEDURE sp_write_tag (
   input1    IN       VARCHAR2,
   input2    IN       VARCHAR2,
   output2   OUT      VARCHAR2)
IS
BEGIN
   output2 := input2;

   BEGIN
      -- Log Data
      p ('Trying to Log First Time');
      pkg_tag_hist.log_tag (input1, input2, output2);
      p ('Data Logged First Time SUCCESS');
   EXCEPTION
      WHEN OTHERS THEN
         p ('Data Logged First Time ERROR = ' || SQLERRM);
         p ('Trying to Log Second Time');
         -- It it fails for some reason, try again
         pkg_tag_hist.log_tag (input1, input2, output2);
         p ('Data Logged Second Time SUCCESS');
   END;

   COMMIT;
END;
/

-----------------------------------------
SET Serveroutput ON

DECLARE
   input1    VARCHAR2 (200);
   input2    VARCHAR2 (200);
   output2   VARCHAR2 (200);
BEGIN
   input1 := 'Test1';
   input2 := '99';
   output2 := NULL;
   sp_write_tag (input1, input2, output2);
   DBMS_OUTPUT.put_line ('OUTPUT2 = ' || output2);
   COMMIT;
END;
/

Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

----------------------------------------
in a seperate session do

SQL> alter table TAG_VALUE_HIST drop partition TAG_2006_P0053;

Table altered.

-----------------------------------------

SET Serveroutput ON

DECLARE
   input1    VARCHAR2 (200);
   input2    VARCHAR2 (200);
   output2   VARCHAR2 (200);
BEGIN
   input1 := 'Test1';
   input2 := '99';
   output2 := NULL;
   sp_write_tag (input1, input2, output2);
   DBMS_OUTPUT.put_line ('OUTPUT2 = ' || output2);
   COMMIT;
END;
/

Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.

SQL> /
Trying to Log First Time
Data Logged First Time ERROR = ORA-06508: PL/SQL: could not find program unit
being called
Trying to Log Second Time
Data Logged Second Time SUCCESS
OUTPUT2 = 99

PL/SQL procedure successfully completed.



 

Tom Kyte

Followup  

June 12, 2006 - 10:14 am UTC

I run this in 9ir2, 10gr2 and it never "doesn't happen on the first try"

your package does not have a state, I don't have the issue at all.


Not until I rewrite your example to have the package MAINTAIN a state do I see an issue.


so, rework the example to mimick what you are really doing (PLEASE cut and paste your exact example JUST LIKE I AM BELOW:)

I also assumed that your "p" procedure (not included in the small, yet 100% complete...) is simply a call to dbms_output and I supplied my own version of that.



ops$tkyte@ORA9IR2> CREATE TABLE TAG_VALUE_HIST
  2  (
  3    ACTION_DT  TIMESTAMP(3)                       NOT NULL,
  4    TAG_NAME   VARCHAR2(256)                      NOT NULL,
  5    IN_VALUE   VARCHAR2(256)                          NULL,
  6    OUT_VALUE  VARCHAR2(256)                          NULL
  7  )
  8  PARTITION BY RANGE (ACTION_DT)
  9  (
 10    PARTITION TAG_2006_P0053 VALUES LESS THAN (TIMESTAMP'2006-06-10 18:00:00'),
 11    PARTITION TAG_2006_P0054 VALUES LESS THAN (TIMESTAMP'2006-06-15 00:00:00'),
 12    PARTITION TAG_2006_P0055 VALUES LESS THAN (TIMESTAMP'2006-06-20 06:00:00')
 13  )
 14  NOCACHE
 15  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE PACKAGE pkg_tag_hist
  2  AS
  3     PROCEDURE log_tag (
  4        input1    IN   VARCHAR2,
  5        input2    IN   VARCHAR2,
  6        output2   IN   VARCHAR2);
  7  END;
  8  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE PACKAGE BODY pkg_tag_hist
  2  IS
  3     PROCEDURE log_tag (
  4        input1    IN   VARCHAR2,
  5        input2    IN   VARCHAR2,
  6        output2   IN   VARCHAR2)
  7     IS
  8     BEGIN
  9        INSERT INTO tag_value_hist
 10             VALUES (SYSTIMESTAMP, input1, input2, output2);
 11     END;
 12  END;
 13  /
 
Package body created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE OR REPLACE PROCEDURE sp_write_tag (
  2     input1    IN       VARCHAR2,
  3     input2    IN       VARCHAR2,
  4     output2   OUT      VARCHAR2)
  5  IS
  6  BEGIN
  7     output2 := input2;
  8
  9     BEGIN
 10        -- Log Data
 11        p ('Trying to Log First Time');
 12        pkg_tag_hist.log_tag (input1, input2, output2);
 13        p ('Data Logged First Time SUCCESS');
 14     EXCEPTION
 15        WHEN OTHERS THEN
 16           p ('Data Logged First Time ERROR = ' || SQLERRM);
 17           p ('Trying to Log Second Time');
 18           -- It it fails for some reason, try again
 19           pkg_tag_hist.log_tag (input1, input2, output2);
 20           p ('Data Logged Second Time SUCCESS');
 21     END;
 22
 23     COMMIT;
 24  END;
 25  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> show errors
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> -----------------------------------------
ops$tkyte@ORA9IR2> SET Serveroutput ON
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> DECLARE
  2     input1    VARCHAR2 (200);
  3     input2    VARCHAR2 (200);
  4     output2   VARCHAR2 (200);
  5  BEGIN
  6     input1 := 'Test1';
  7     input2 := '99';
  8     output2 := NULL;
  9     sp_write_tag (input1, input2, output2);
 10     DBMS_OUTPUT.put_line ('OUTPUT2 = ' || output2);
 11     COMMIT;
 12  END;
 13  /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> alter table TAG_VALUE_HIST drop partition TAG_2006_P0053;
 
Table altered.
 
ops$tkyte@ORA9IR2> SET Serveroutput ON
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> DECLARE
  2     input1    VARCHAR2 (200);
  3     input2    VARCHAR2 (200);
  4     output2   VARCHAR2 (200);
  5  BEGIN
  6     input1 := 'Test1';
  7     input2 := '99';
  8     output2 := NULL;
  9     sp_write_tag (input1, input2, output2);
 10     DBMS_OUTPUT.put_line ('OUTPUT2 = ' || output2);
 11     COMMIT;
 12  END;
 13  /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> /
Trying to Log First Time
Data Logged First Time SUCCESS
OUTPUT2 = 99
 
PL/SQL procedure successfully completed.
 
 

Scott

July 07, 2006 - 7:36 am UTC

Reviewer: A reader from Glasgow

Tom,

Under what circumstances can the timestamp of a referenced package specification be newer than the timestamp of the dependent package body that calls it without the package body of the dependend package being invalid? I have just such a situation. When I call a procedure in the dependent package I get the oracle errors:

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "TESTUSER.PKG_B"
ORA-06508: PL/SQL: could not find program unit being called: "TESTUSER.PKG_B"
ORA-06512: at "TESTUSER.PKG_A", line 292
ORA-06512: at line 4

Even though PKG_A and PKG_B both exist and are VALID.

Compiling the body of PKG_A makes it's timestamp newer again than the specification of PKG_B, and the problem goes away.

I understand that there are "optimisations" which with each version of Oracle reduces the number of invalids (or at least I think I read that somewhere) but what is the point of that when I clearly can't use PKG_A until I recompile it?

Kind regards,

Scott.

Tom Kyte

Followup  

July 08, 2006 - 10:49 am UTC

someone recompiled it whilst you were using it.

that is, you used it (instantiated the state)

someone then compiled it (doesn't need to go invalid, doesn't need to be currently invalid, in fact I'd be surprised if YOU were able to observe it being invalid since the simple act of referencing it will recompile it and validate it!!)

and you tried to use it again (getting the error).



if you re-invoke the package, it should just "go", you need not recompile anything.

timestamp / last_ddl_time i ssue

July 10, 2006 - 7:08 am UTC

Reviewer: Scott from Glasgow

Hi, Tom.

The package is never recompiled.

In fact, no matter how many times I run it, I get the same error.

Because the package is "VALID" I suppose Oracle does not recompile or revalidate the package.

select main.object_name main, main.object_type main_type,
used.object_name used, used.object_type used_type,
main.timestamp main_timestamp, used.timestamp used_timestamp, main.last_ddl_time main_last_ddl, used.last_ddl_time used_last_ddl,
main.status main_status, used.status used_status
from user_dependencies dep, user_objects main, user_objects used
where dep.name = main.object_name
and dep.type = main.object_type
and dep.referenced_name = used.object_name
and dep.referenced_type = used.object_type
and to_date( main.timestamp, 'yyyy-mm-dd:hh24:mi:ss' ) < to_date( used.timestamp , 'yyyy-mm-dd:hh24:mi:ss' )
and main.last_ddl_time < used.last_ddl_time;

(output formatted for easier reading)
main A
main_type PACKAGE BODY
used B
used_type PACKAGE
main_timestamp 2006-07-03:14:53:59
used_timestamp 2006-07-04:15:48:26
main_last_ddl 03/07/2006 14:53:59
used_last_ddl 04/07/2006 15:48:31
main_status VALID
used_status VALID

(A.Proc1 calls a procedure in package B)

declare
nRes integer;
szFilter varchar2(32);
begin
A.Proc1( nRes, szFilter );
end;

The following error has occurred:

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "TESTUSER.B"
ORA-06508: PL/SQL: could not find program unit being called: "TESTUSER.B"
ORA-06512: at "TESTUSER.A", line 25
ORA-06512: at line 5

select ...
from user_dependencies . ..

(output formatted for easier reading)
main A
main_type PACKAGE BODY
used B
used_type PACKAGE
main_timestamp 2006-07-03:14:53:59
used_timestamp 2006-07-04:15:48:26
main_last_ddl 03/07/2006 14:53:59
used_last_ddl 04/07/2006 15:48:31
main_status VALID
used_status VALID


declare
nRes integer;
szFilter varchar2(32);
begin
A.Proc1( nRes, szFilter );
end;

The following error has occurred:

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "TESTUSER.B"
ORA-06508: PL/SQL: could not find program unit being called: "TESTUSER.B"
ORA-06512: at "TESTUSER.A", line 25
ORA-06512: at line 5

...

alter package A compile body;

select main.object_name main, main.object_type main_type, used.object_name used, used.object_type used_type,
main.timestamp main_timestamp, used.timestamp used_timestamp, main.last_ddl_time main_last_ddl, used.last_ddl_time used_last_ddl, main.status
from user_dependencies dep, user_objects main, user_objects used
where dep.name = main.object_name
and dep.type = main.object_type
and dep.referenced_name = used.object_name
and dep.referenced_type = used.object_type
and main.object_name = 'A'
and used.object_name = 'B'
and main.status = 'VALID';

main A
main_type PACKAGE BODY
used B
used_type PACKAGE
main_timestamp 2006-07-10:11:48:06
used_timestamp 2006-07-04:15:48:26
main_last_ddl 10/07/2006 11:48:06
used_last_ddl 04/07/2006 15:48:31
main_status VALID
used_status VALID

declare
nRes integer;
szFilter varchar2(32);
begin
A.Proc1( nRes, szFilter );
end;

PL/SQL procedure successfully completed

Tom Kyte

Followup  

July 10, 2006 - 8:32 am UTC

is this distributed? are A and B in the same database?

last_ddl_time / timestamp issue

July 10, 2006 - 10:17 am UTC

Reviewer: Scott from Glasgow

Hello Tom.

It is not distributed. Everything is in the same schema, database and server.

Somehow Oracle has allowed the specification of a package to be updated without invalidating the dependent packages that call it.

Tom Kyte

Followup  

July 10, 2006 - 12:33 pm UTC

Not necessarily - I can easily reproduce the timestamp issue, all it takes is sessions have different TZ settings for example.  The validity checks all work - but the timestamps get botched because of the SYSDATE values being returned differently in different sessions:

[tkyte@xtkyte-pc tkyte]$ export TZ=EST
[tkyte@xtkyte-pc tkyte]$ plus
 
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 10 11:20:04 2006
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
ops$tkyte@ORA10GR2> select to_char(sysdate,'hh24:mi:ss') from dual;
 
TO_CHAR(
--------
11:20:16
 
ops$tkyte@ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@xtkyte-pc tkyte]$ export TZ=EDT
[tkyte@xtkyte-pc tkyte]$ plus
 
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 10 16:20:21 2006
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
ops$tkyte@ORA10GR2> select to_char(sysdate,'hh24:mi:ss') from dual;
 
TO_CHAR(
--------
16:20:22


depending on which session compiled A, to make it valid (because if B went invalid - A surely did) - the last_ddl_time would be set to one of the above two values..

But I cannot reproduce your issue where it will never get over the 4068 - unless you have a test case I can work with, I'll have to suggest working with support - they can set some events to help track down what is happening.

 

Query to find Local subprograms in a package

July 11, 2006 - 2:51 am UTC

Reviewer: SKL from Bangalore

Hi Tom,
Thanks in advance for your wonderful contributions.

i would want a query which would give me details of private functions/procedures in a package.

To be more clear... suppose there a package A, with some 100 subprograms A1,A2....A100. Among these only 30 are defined inside package specificaiton and rest 70 are private.

i want a query which give the details about these 70 private programs.

Thanks once again

Tom Kyte

Followup  

July 11, 2006 - 7:52 pm UTC

you would have to parse the plsql source code - that information is not really stored anywhere.

you would have to query all_source (or dba/user_source) and "parse it out"

Procedure within a procedure

October 09, 2006 - 12:16 pm UTC

Reviewer: Kumar from NY

Tom,

Appreciate if you could find a few moments to reply to this:
(Excuse me if this is not related to the thread)

Which is better: To have a procedure in the declarative section of a procedure or call a separate procedure?

Create or replace procedure proc1
as
PROCEDURE n1
is
begin
dbms_output.put_line('inside n1');
end;
begin
null;
end;

Instead of having a separate procedure n1 and calling within the body of the above proc1.

Will there be performance gains when declared within a procedure?

Also, when would it be advisable to code like the above PROC1?

Tom Kyte

Followup  

October 09, 2006 - 1:35 pm UTC

none of the above.

the correct answer for me would be:

c) use a package


then proc1 would be in the spec. n1 would only be in the body and hidden from all except other procedures/functions in that body.

a procedure is a procedure from a performance perspective.

Great!

October 09, 2006 - 2:07 pm UTC

Reviewer: Kumar

Thanks Tom for the answer and the time.

what can cause massive packages invalidations

December 15, 2006 - 7:00 am UTC

Reviewer: A reader

Hi

We are running Oracle Applications 11.5.10.2 (latest version), suddently this week, this happened two days, packages become invalid. Over 25000 packages suddently become invalid!

We dont know how can this happen? What can cause such invalidations.



Tom Kyte

Followup  

December 15, 2006 - 9:00 am UTC

revokes of privileges (if you really want to ruin your day grant select any table to public and then revoke it! DO NOT REALLY DO THAT, it'll be painful)


recompilation of a package specification - that causes other packages to go invalid, which causes other packages to go invalid, which....

privilege?

December 15, 2006 - 6:01 pm UTC

Reviewer: A reader

Hi

I dont understand why grant any table to public then revoke it can cause mass invalidations?

Tom Kyte

Followup  

December 16, 2006 - 5:54 pm UTC

You write a procedure against a table "SCOTT.EMP"

Now, you could have the ability to access scott.emp because:

o you were granted access to it via "grant select"
o public had access to it


we only realize that you have access to it - we do not record HOW you got access to it when you compile. therefore, if you are revoked any privilege that could have been used to gain access to scott.emp, we need to recompile in order to verify that you STILL have access to scott.emp

Package subprograms

January 12, 2007 - 1:48 am UTC

Reviewer: prasanth

Hi Tom,
We need your support on finding dependencies between a table and procedures which are packaged. this is because table structure got changed and we need to create IAD report.

Regards,
Prasanth.PV

why should creating a Foreign Key invalidate cursor on parent table

April 12, 2007 - 9:59 am UTC

Reviewer: A reader

Hi Tom.

When we create a foreign key constraint, it invalidates the cursors that depends on parent table. Why should it do so? As nothing in the parent table has changed.

We are on 10.1.0.4.

Following is the test case.

1. BMC_MANUSCRIPT table is the parent table and quite a lot of queries use this table. This table already exists in the database.

2. bmc_manuscript_class_b is the child table [and needs to be created].

Referential integrity on bmc_manuscript_class_b table is

ALTER TABLE bmc_manuscript_class_b ADD
(
CONSTRAINT bmc_mcl_man_id_fk_b
FOREIGN KEY (mcl_man_id)
REFERENCES bmc_manuscript(man_id)
ON DELETE CASCADE
NOVALIDATE
);

Test case:

a. I exeute a query on bmc_manuscript table

select /* bipul_k*/ count(*) from bmc_manuscript ;

b. Find out the cursor information from v$sql.

select sql_text, executions, invalidations, first_load_time, last_load_time, parse_calls from v$sql where sql_text like '%bipul_k%bmc_manuscript%' ;

SQL_TEXT EXECUTIONS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME PARSE_CALLS
select /* bipul_k*/ count(*) from bmc_manuscript 1 0 2007-04-12/14:42:22 2007-04-12/14:42:22 1


c. Create child table

CREATE TABLE bmc_manuscript_class_b
(
mcl_man_id NUMBER(16),
mcl_rank NUMBER(1),
mcl_cla_id NUMBER(16) NOT NULL
)
TABLESPACE bmc_data_tablespace
MONITORING;

ALTER TABLE bmc_manuscript_class_b ADD
(
CONSTRAINT bmc_mcl_pk_b
PRIMARY KEY (mcl_man_id, mcl_rank)
USING INDEX TABLESPACE bmc_index_tablespace
);

Note that I havn't created the foreign key constraint yet.
Check the status of cursor on bmc_manuscript table.

select sql_text, executions, invalidations, first_load_time, last_load_time, parse_calls from v$sql where sql_text like '%bipul_k%bmc_manuscript%' ;

SQL_TEXT EXECUTIONS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME PARSE_CALLS
select /* bipul_k*/ count(*) from bmc_manuscript 1 0 2007-04-12/14:42:22 2007-04-12/14:42:22 1

Still same, no change in here.

Now create foreign key constraint.

ALTER TABLE bmc_manuscript_class_b ADD
(
CONSTRAINT bmc_mcl_man_id_fk_b
FOREIGN KEY (mcl_man_id)
REFERENCES bmc_manuscript(man_id)
ON DELETE CASCADE
NOVALIDATE
);

and check the status of the cursor.

select sql_text, executions, invalidations, first_load_time, last_load_time, parse_calls from v$sql where sql_text like '%bipul_k%bmc_manuscript%' ;

SQL_TEXT EXECUTIONS INVALIDATIONS FIRST_LOAD_TIME LAST_LOAD_TIME PARSE_CALLS
select /* bipul_k*/ count(*) from bmc_manuscript 0 1 2007-04-12/14:42:22 0

Its invalidated. I can;t understand why it should, as I havn't done any change on bmc_manuscript table.

This has limitation on what changes we can make to the system while its in use and so on the availablity of the system. If we make change while system is in use, then we run into the risk of having serious latch issue on

library cache pin
library cache lock

which in a very busy database can only be resolved by restarting the instance.

thanks
bipul








Tom Kyte

Followup  

April 13, 2007 - 12:05 pm UTC

because the optimizer looks at constraints, as well as statistics and the other metadata, when deciding on query plans.

It is like adding an index, new opportunities for access paths open up in the presence of a new constraint.

http://asktom.oracle.com/Misc/stuck-in-rut.html

You'll be getting a DDL lock on the table anyway here - the invalidation of the cursors is trivial compared to that.

and it would not require restarting the instance.

online compiling of procedure

May 30, 2007 - 10:15 am UTC

Reviewer: abz

I have a procedure in which I do following

execute immediate 'DROP TABLE t1';
..do some work....
execute immediate 'CREATE TABLE t1 AS SELECT * FROM t2';

FOR I IN (SELECT * FROM T1) LOOP

...
END LOOP.

Now if I start this procedure and interupt in between
and by that time it has dropped the T1 table but not
created it, the procedure will become invalid.
How can I overcome this problem.

Tom Kyte

Followup  

May 30, 2007 - 3:49 pm UTC

oh my gosh

stop it, just stop it, you are hurting my head here.



Please do not do DDL like that - why would you do that, truncate if you have to, but do not constantly drop and create schema objects. Just stop.

reason of drop/create

June 04, 2007 - 6:00 am UTC

Reviewer: abz

Actualy if I dont drop/create then I will have
to TRUNCATE/INSERT INTO t1 AS SELECT * FROM t2.

But I observed that CREATE TABLE t1 AS SELECT * FROM t2
is faster than INSERT INTO t1 SELECT * FROM T2.

Tom Kyte

Followup  

June 05, 2007 - 8:22 am UTC

truncate / insert /*+ APPEND */ into ...


when you create table as select, you do a direct path load.

when you insert into - you do a conventional path load.
when you insert /*+ append */ into - you do a direct path load (if possible, triggers and certain constraints would make it conventional path - but then you could just disable them, load and then enable them)

Back to the original question (and your answer)

October 08, 2007 - 4:36 pm UTC

Reviewer: A reader from Orlando, FL USA

Okay - you say "don't use standalone procedures or functions" - and the reason you give is dependancy. But - from a tuning perspective... If I am trying to eliminate redundancy in our code, and yet want to have "like" code together in packages - then for example my goal is to do away with 40 re-written INSERT statements into the same table, and just have one. But when I make that one INSERT statement (of course also keeping in mind that an INSERT statement will likely not change very often) that is used all over several different major application areas within a huge system - where should I put it?
I've read elsewhere that packages do have overhead involved with them and "if you don't need a package don't use one". So all my little INSERT statements are really sort of stand alone - since for an example I could have one INSERT into a table used from two different areas - one which only runs at night and another that runs only during the day. I still don't want duplicated code, but then how should I package my INSERT. Should I have a "INSERT" package? Or put all the inserts for related tables in a database in one Package - or does it not matter since only the piece of code that I need will be pulled in and not the possibly large package around it??? And with the "small chunk" paging - then is there no performance benefit to having related code in a package together? Or is the benefit only in maintenance.

Followup question on your response

October 16, 2007 - 9:14 pm UTC

Reviewer: Menon

Hi Tom
Please see above
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:286816015990#51843630423250

I tried capturing the exception in JDBC and reexecuting the PL/SQL package. Assume the following:
1. The production code runs version 1 of the package.
2. We change the package to version 2 and compile it in production.
3. The production code's next run should just use the new version of the package with the new state of the package (e.g. of a global constant was changed, use the new value and so on.) In this case, if we catch the exception at JDBC level and reexecute the package one more time, it seems to work in my tests (I have not tested this extensively yet.) Assume that any state in the package is either a constant or is always initialized from tables (e.g. a short cache in memory for frequently used data.) Do you see any issues with this approach?

An article I wrote on ORA-04068 error

October 22, 2007 - 2:55 pm UTC

Reviewer: Menon

Hi Tom
Yesterday I finished an article on how to deal with ORA-04068 at my blog at:

http://dbj2ee.blogspot.com/2007/10/dealing-with-oracle-plsql-error-ora.html

I am taking the liberty of posting the link here since the article seems to be relevant to this thread. If for some reason you disagree, please feel free to delete this post.

Most importantly, I would appreciate your comments and other reader's comments on this article either here or on the blog. I may be advocating this technique in our company and what better way to validate an idea than to post it so public can have a go at it:)
Tom Kyte

Followup  

October 23, 2007 - 11:20 am UTC

my quick comments:

solutions #3 and #4 - if they apply, then the real solution is #1, because apparently the state is NOT RELEVANT to the correct functioning of the code - hence....

use #1

I'm not a fan of #3 and #4 - you are running with an unknown state - the programmers of the packages might have two packages that work in tandem and you've reset the state of one but not the other - you just never know.

Better to be safe - do not use package state if this is an issue for you.

In a connection pool, having a package state is a questionable thing anyway.

(i only quickly glanced at your article, if I have time later, I'll look in more detail)

Sure but....

October 23, 2007 - 8:32 pm UTC

Reviewer: Menon

thanx for your comments!

"solutions #3 and #4 - if they apply, then the real solution is #1, because apparently the state is NOT RELEVANT to the correct functioning of the code - hence....
use #1 "

Not really. No # 1 precludes all state including constants which does not make sense to me and is too restricting as I mentioned in the article. Also for legacy code, this may not even be feasible.

"
I'm not a fan of #3 and #4 - you are running with an unknown state - the programmers of the packages might have two packages that work in tandem and you've reset the state of one but not the other - you just never know.

Better to be safe - do not use package state if this is an issue for you. "

Well, I tested my code in the article with simple cases. And the re-execution always worked. The only case I can think of when it will fail is when one execution of a method in the package depends on its results for the previous state of the package.

So can you think of a concrete example, that would not work with re-execution?

"In a connection pool, having a package state is a questionable thing anyway. "

Well, not if you are only using constants :)

"(i only quickly glanced at your article, if I have time later, I'll look in more detail)"

Please do - I would love to hear your input. Other readers also can please chime in.

Thank you!

Tom Kyte

Followup  

October 24, 2007 - 8:58 am UTC

regarding constants - your "put them in their own package" works. If you change a constant - we are right back into the "we are in a totally unknown state - half run with X=5, half run with X=10". We need to destroy that session and start over, unless you yourself wrote all of the plsql code and can state with 100% degree of certainty that it is OK (and that never happens)

I will stick with #3 and #4 as being so shaky as to be labeled "do not do it, destroy the session and re-establish a new one"

especially if you change a constant
especially for legacy code (that has dependencies you cannot even imagine since you did not write it)


... execution of a method in the package depends on its results for the previous
state of the package. ...

that is precisely what I was talking about (in tandem)

I'll stick with "destroy session, start over, be safe".

Thanx

October 24, 2007 - 10:06 am UTC

Reviewer: Menon

"regarding constants - your "put them in their own package" works. If you change a constant - we are right back into the "we are in a totally unknown state - half run with X=5, half run with X=10". We need to destroy that session and start over, unless you yourself wrote all of the plsql code and can state with 100% degree of certainty that it is OK (and that never happens) "

I agree with you partially.
Putting them in their own package (i.e. creating a companion constant package for each package) would definitely reduce the number of ORA-04068 as I mention in the article. Typically people have one constant package for all packages but that is not a good idea as discussed in the article. However, if you have hundreds of packages, then this would lead to a massive rewrite - so for a new system this is ok but for an existing system this may not be feasible (I mention this also in the article.)

Now, what I dont understand is your next statement. Consider that you grab a connection (connection pool) on day 1 and execute a package procedure with constant value of 1. The procedure finishes execution. Now at night you change the constant value to 2 by redeploying the package. In all cases I can think of, the next day - the expectation is that your next execution should simply pick up the new constant and take it from there. The results would not change whether you flush the connection or not. The execution of a package method depends on the state it was before the exeution started and once it finished execution, you should be able to set the original state to something else and the next execution should simply be independent of the prior execution.
So in other words, all examples I know of in my system (and I believe in general this is true for most systems) where constants are concerned is where one execution of a package method just depends on the current value of the constant and if it changed next time, the next execution should simply pick it up from there. Thus I dont see any difference between two executions whether you flush the connection or not.

"I will stick with #3 and #4 as being so shaky as to be labeled "do not do it, destroy the session and re-establish a new one" "

This is where I would appreciate a real example where this would matter. I would like to see an example- ideally involving constants - where there is a difference in re-executing from the same session or destroying the session and re-executing it. I can think of some but they all seem very contrived to me - I think you may have a more real world example in mind.

Thanx!


Tom Kyte

Followup  

October 24, 2007 - 3:25 pm UTC

you grab a connection, it has a constant 1.

15 other packages are loaded by that session and they did stuff based on "1", they sized something, they copied this variable, they initialized themselves based on that. They accessed this constant via a 'getter' routine in the body, they are not going to be recompiled because the body changes...

you recompile the package and change the constant to 2 - they (the other packages) are sort of out of sync.

You are making a bunch of assumptions based on the way you code, it is not written in stone that you must code that way - especially given that you keep throwing out 'legacy code', you have no idea how twisted that code could be.

I will remain firm - if you compile code into a running system right now - you are on shaky grounds.

I just gave you an example - if someone reads your constants and makes decisions based on them, then you change the constants (and they are not themselves reloaded) - you could be hosed - just think of the possibilities.

Doing anything after that is shaky, unless you are so intimate with the code you can state "it'll not be a problem"


constants - by their very name - are presumed to be constant by developers, you are making constants "not constant". If someone copies a constant for whatever reason ad you change it - bam, you got trouble.

Here is an example

October 24, 2007 - 8:55 pm UTC

Reviewer: Menon

Thank you Tom.
You said:
"you grab a connection, it has a constant 1. 

15 other packages are loaded by that session and they did stuff based on "1", they sized something, they copied this variable, they initialized themselves based on that. They accessed this constant via a 'getter' routine in the body, they are not going to be recompiled because the body changes... 

you recompile the package and change the constant to 2 - they (the other packages) are sort of out of sync. 
"

----
Now, here is an example that I think mimics what you say - but may be I am missing a condition that you can add:

First we create the schema:

SQL> create table test(x number);

Table created.

Then we create a package with one constant initialized to 1:

SQL> @test_const_pkg_p
SQL> create or replace package test_const_pkg_p
  2  as
  3    c_constant constant number := 1;
  4  end;
  5  /

Package created.

SQL> show errors;
No errors.

We create two packages test_pkg_p and test_pkg1_p that each have a procedure called test_insert. This procedure simply inserts the constant in test_const_pkg_p into table test and commits.


SQL>
SQL> @test_pkg_p
SQL> create or replace package test_pkg_p
  2  as
  3    procedure test_insert;
  4  end;
  5  /

Package created.

SQL> show errors;
No errors.

SQL>
SQL> @test_pkg_b
SQL> create or replace package body test_pkg_p
  2  as
  3    procedure test_insert
  4    is
  5    begin
  6      insert into test(x) values(test_const_pkg_p.c_constant);
  7      commit;
  8    end test_insert;
  9  end;
 10  /

Package body created.

SQL> show errors;
No errors.

SQL> @test_pkg1_p
SQL> create or replace package test_pkg1_p
  2  as
  3    procedure test_insert;
  4  end;
  5  /

Package created.

SQL> show errors;
No errors.

SQL> @test_pkg1_b
SQL> create or replace package body test_pkg1_p
  2  as
  3    procedure test_insert
  4    is
  5    begin
  6      insert into test(x) values(test_const_pkg_p.c_constant);
  7      commit;
  8    end test_insert;
  9  end;
 10  /

Package body created.

SQL> show errors;
No errors.


Thus packages test_pkg_p and test_pkg1_p are dependent on test_const_pkg_p.

We now log in to two sessions - session 1 and session 2:
In session 2, we execute the procedure in both packages and verify the result:
session 2>exec test_pkg_p.test_insert

PL/SQL procedure successfully completed.

session 2>exec test_pkg1_p.test_insert

PL/SQL procedure successfully completed.

session 2>@sel
session 2>select *
  2  from test;

         X
----------
         1
         1


In session 1 we compile the package test_const_pkg_p after changing the value of the constant to 2.

session 1>create or replace package test_const_pkg_p
  2  as
  3    c_constant constant number := 2;
  4  end;
  5  /

Package created.

session 1>show errors;
No errors.

Sure enough, both our packages are invalid:
session 1>@invalid
session 1>set head off
session 1>break on object_type skip 1
session 1>column status format a10
session 1>select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  and object_name like 'TEST%'
  5  order by object_type, object_name;

PACKAGE BODY
TEST_PKG1_P
INVALID


TEST_PKG_P
INVALID



session 1>

Now in session 2, we execute test_pkg_p.test_insert and get ORA-04068 as expected:

session 2>exec test_pkg_p.test_insert
BEGIN test_pkg_p.test_insert; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "HERCULES.TEST_CONST_PKG_P" has been
invalidated
ORA-04065: not executed, altered or dropped package "HERCULES.TEST_CONST_PKG_P"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "HERCULES.TEST_PKG_P", line 6
ORA-06512: at line 1

We rexecute it and it works fine as we want it to - inserting the new value of the constant into the table:

session 2>exec test_pkg_p.test_insert

PL/SQL procedure successfully completed.

session 2>select *
  2  from test;

         X
----------
         1
         1
         2

And the second package works fine as well:
session 2>exec test_pkg1_p.test_insert

PL/SQL procedure successfully completed.

session 2>@sel
session 2>select *
  2  from test;

         X
----------
         1
         1
         2
         2
---

How would you modify your example so that a stale constant is retained by one of the sessions?

Thanx!
Menon

Slight modification to the question above

October 24, 2007 - 9:01 pm UTC

Reviewer: Menon

"How would you modify your example so that a stale constant is retained by one of the sessions?"

Should read as

"
How would you modify your example so that a stale constant is retained by the second session?"

Tom Kyte

Followup  

October 25, 2007 - 5:53 pm UTC

use a getter function, do not be dependent on the spec.


but forget constants, just think about you calling a function that is supposed to be basically deterministic - and you make decisions on that and then you blow away that assumption.

Unless you own every byte of the code - I'm sorry but you cannot tell me that it is "safe"

Hmmm.....

October 25, 2007 - 7:45 pm UTC

Reviewer: Menon

"use a getter function, do not be dependent on the spec.

but forget constants, just think about you calling a function that is supposed to be basically deterministic - and you make decisions on that and then you blow away that assumption.

Unless you own every byte of the code - I'm sorry but you cannot tell me that it is "safe" "

OK - I still dont get it, sorry. Can you please tell me how to modify my example - you dont have to do it - just tell me how I modify it so I can reproduce it. For me, if after compilation of a package - all other packages - in their next execution get the latest value automatically and transparently - that is ok. So either you are saying that - that is not ok - or you are telling me that there is a scenario where that assumption will not be true. Please clarify and sorry once again for not getting it after so many back and forths.
PS: I did create another test case where instead of the constant in a package I was dependent on a getter function whose implemtnation returned a constant. But that one did not even raise ORA-04068 because the packages were stateless :) I would love to see a simple example where a session retains an old value and runs a method based on the old value after a package it depends on is compiled in another session.

Tom Kyte

Followup  

October 29, 2007 - 10:25 am UTC

do you see how "lobotomizing" part of your code - in a non-database application (think your "java code") could be a disaster?

I am not saying that anyone uses an "old value", I'm saying someone COPIES a value that is not supposed to change into their own space - and then it DOES CHANGE.


You tell me "I will return to you a value, it will never change"

I say great, what is that value
and you tell me "5"

Now, I go off and make stateful decisions about the fact you said "5"

Later, you tell someone else (other code) IN THE SAME SESSION, "I will return to you a value, it will never change"

They say great, what is that value
and you tell them "42"

And they go off and make stateful decisions about the fact you said "42"


do you see how that could be "a problem"

Thanx - it is clear now

October 29, 2007 - 1:36 pm UTC

Reviewer: Menon

Yes. I was trying to differentiate the two solutions for deployment.
1) You flush all your sessions - the normal way
2) You dont and silently re-execute the package like I had suggested.

Consider the scenario where you are executing a procedure p1. The first time it gets the constant value of 1 and the second time (sicne someone silently changed the constant to 2) in the same session you get the value of 2. This would be a problem as Tom correctly states. The thing I want to note is that by choosing the solution 1 (flushing all connections before deploying any PL/SQL package), you have a brute force method where you ensure that in one session a constant value can not change. The brute force method leads to a downtime but there are no two ways to it. The alternative is to use the "silent re-execution" technique but then you have to be damn sure there are no transactions going on in the database - which is very hard to do. By flushing the connection pool, you ensure that.

Tom, let me know if you agree with this or add something to it or qualify it. I will update the article as soon as I can and of course point to this discussion as acknowledgement.
Tom Kyte

Followup  

October 30, 2007 - 1:07 pm UTC

... there are no
transactions going on in the database ..

no, you have to do more than that - you have to make sure there are NO SESSIONS - or be sure that no sessions rely in any way shape or form on a 'state' (in which case, we are back to square one - you don't have any state!!!!)


And the right solution is

October 29, 2007 - 1:41 pm UTC

Reviewer: Menon

to have a stateless package with constant values defined as part of a function that returns the value based on a string (kind of like a hashmap.) Slightly costly but infinitely preferable to downtimes when we deploy packages. i will also mention this solution as the final solution that I prefer (with acknowledgements to you.)

Sorry about that

October 29, 2007 - 2:01 pm UTC

Reviewer: Menon

I posted in haste. It does nt matter whether you have real constants in a package or you have a function that returns "constants" based on string values. In fact it is better to have proper constants so dependencies are clear. Bottom line is:
1. Use stateless packages
2. Any constants, if you have to define can be defined in a central package (or packages) and you have downtime deployment only when one these packages changes.

Thanx.

October 30, 2007 - 2:48 pm UTC

Reviewer: Menon

"... there are no
transactions going on in the database ..

no, you have to do more than that - you have to make sure there are NO SESSIONS - or be sure that no sessions rely in any way shape or form on a 'state' (in which case, we are back to square one - you don't have any state!!!!)
"
Yes - that is more precise and simpler way of putting it. Stateless packages (and hence sessions) is what I suggest in my last post and what you suggested in the beginning as the better approach compared to other approaches including the final one I recommended in the article - one can not admit one's mistake more plainly:)
The only thing is that I suggest having constants in a central package (or packages) which when changed (should happen rarely in most cases) are understood to have a state change (and a requirement for the connection pools to be flushed.) Banning constants altogether seems to be too harsh a rule to me. This last point about constants is a relatively subjective one though - depends on how much you like constants.

I have updated my article

November 04, 2007 - 9:42 pm UTC

Reviewer: Menon

Please check it out at http://dbj2ee.blogspot.com/2007/10/dealing-with-oracle-plsql-error-ora.html . If there are any more comments or corrections, let me know.


adding procedure to an existing package

November 27, 2007 - 4:41 am UTC

Reviewer: Rajat from India

Hi Tom,

There is a situation where we have to generate a package script dynamically - please do not ask why :)

Hence the question follows, is there a way we can add a procedure/function to an existing package?

Thanks!
Tom Kyte

Followup  

November 27, 2007 - 3:42 pm UTC

you may only create or replace packages and package bodies, there is no "alter and please add this bit"

But 11g may make a big difference

November 28, 2007 - 7:24 am UTC

Reviewer: Mike from Cleveland, OH USA

The dependency management is very, very different in 11g.
See the New Features Guide:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28279/chapter1.htm#NEWFTCH1
and search for
1.2.9.3 Finer Grained Dependencies

Adding a new procedure to a package spec will generally not cause object invalidations.

Tom Kyte

Followup  

November 28, 2007 - 10:59 pm UTC

correct...

things change...


but - packages still rule.

August 07, 2008 - 5:09 am UTC

Reviewer: jyothsna

Hi tom,

Your answer is good, And i have a query to you
I don't know how to post it to you...
So, please guide me the procedure to post you the queries
Tom Kyte

Followup  

August 07, 2008 - 1:26 pm UTC

when I am taking new questions, there is a link on the home page

ORA-04068 and invalidated package states

October 06, 2008 - 2:15 pm UTC

Reviewer: Mike from Cleveland, OH USA

Is it possible to write a query to identify any sessions that are holding invalidated package states?
Tom Kyte

Followup  

October 06, 2008 - 3:10 pm UTC

not that I am aware of, no

October 06, 2008 - 3:49 pm UTC

Reviewer: Robby

Like MS SQL Server have a partition features too but Oracle RULE. :-)

meaning in the concept guide (auto recompilation once only?)

June 23, 2009 - 1:37 pm UTC

Reviewer: A reader from singapore

hi tom,

i read this in the oracle 10g concept guide on page 155 (chapter 6-3)

it says

Oracle attempts to recompile an invalid object dynamically
only if it has not been replaced since it was detected as invalid. This
optimization eliminates unnecessary recompilations.


i do not quite fully understand what it means.
does it mean that when an object get invalid the 1st time,
oracle will try to recompile it again ?

would be glad if you could show me an example :)
thanks alot!

Regards,
Noob
Tom Kyte

Followup  

June 26, 2009 - 9:15 am UTC

it means that if we detect an invalid procedure, we'll automagically recompile it if possible (as long as it was not replaced - if it was replaced since being detected as invalid - then we know that recompiling it would be useless - it was already recompiled - you replaced it - and that didn't make it valid)


basically - think of it this way:

you do not need to compile invalid plsql bits of code ever. Oracle will automagically compile them upon execution if they can be successfully compiled.

May 05, 2011 - 6:04 pm UTC

Reviewer: Fez from Sydney, Australia

As things change over time....has the reply to the following 3 year old post changed...this is a holy grail to me....

"Is it possible to write a query to identify any sessions that are holding invalidated package states?

Followup October 6, 2008 - 3pm Central time zone:

not that I am aware of, no "


Has any of the newer releases of Oracle exposed some internal 'flag' in a user session that indicates if it has invalid state?

Tom Kyte

Followup  

May 06, 2011 - 10:20 am UTC

Nope, but I can say that in 11g R2, using edition based redefinition, you do not need to experience this issue if you choose not to.

see
http://www.oracle.com/technetwork/issue-archive/index-093676.html

May/June 2010 Looking at Edition-Based Redefinition, Part 3 Our technologist concludes his exploration of Edition-Based Redefinition.

March/April 2010 Edition-Based Redefinition, Part 2 Our technologist continues to explore Edition-Based Redefinition, with a full schema change.

January/February 2010 A Closer Look at the New Edition Our technologist redefines and defers with Oracle Database 11g Release 2.

August 03, 2011 - 11:37 am UTC

Reviewer: A reader

Quick question - I have a package in 10g that is invalid and when I call a function in the package, the function executes fine without any errors.

I have the same package code in 11g, and when i call the same function, i get the following error

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "<pkg>" has been
invalidated
ORA-04065: not executed, altered or dropped package body
"<pkg>"
ORA-06508: PL/SQL: could not find program unit being called:
"<pkg>"
ORA-06512: at line 4


I read something about fine grained dependencies in oracle 11g. Is there a specific scenario that would cause this behaviour ?
Tom Kyte

Followup  

August 03, 2011 - 1:22 pm UTC

It looks like the body is missing in 11g - is that the case?

The same thing could/would happen in 10g - the ora-4068 would happen there just as well if the circumstances where the same.

For example, perhaps in 10g you had not called the package yet in your session - you call it and it would auto-recompile and run.

But in 11g you ran it once - it established a state. Then you destroyed that state by invalidating it in another session and when 11g went to auto-compile - it had to raise the 4068 the first time to let you know its state was destroyed.

If you did the same sequence in 10g, it would 4068 as well.

August 09, 2011 - 2:03 pm UTC

Reviewer: A reader

I tried to recreate the same scenario in both 10g and 11g. I have the same package created in both my 10g and 11g databases.

10g Run
-------

SQL> r
  1  declare
  2  retval char(1);
  3  begin
  4  retval := pkg_test.fnc_test;
  5  dbms_output.put_line(retval);
  6* end;
Y

PL/SQL procedure successfully completed.

SQL> r
  1  declare
  2  retval char(1);
  3  begin
  4  retval := pkg_test.fnc_test;
  5  dbms_output.put_line(retval);
  6* end;
Y

PL/SQL procedure successfully completed.

SQL> r
  1  declare
  2  retval char(1);
  3  begin
  4  retval := pkg_test.fnc_test;
  5  dbms_output.put_line(retval);
  6* end;
Y

PL/SQL procedure successfully completed.

So all the 3 consecutive runs went fine without any errors.

11g Run
--------

SQL> declare
retval char(1);
begin
retval := pkg_test.fnc_test;
dbms_output.put_line(retval);
end;
/

Y

PL/SQL procedure successfully completed.

SQL> SQL> r
  1  declare
  2  retval char(1);
  3  begin
  4  retval := pkg_test.fnc_test;
  5  dbms_output.put_line(retval);
  6* end;
declare
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "schema.PKG_TEST" has
been invalidated
ORA-04065: not executed, altered or dropped package body
"schema.PKG_TEST"
ORA-06508: PL/SQL: could not find program unit being called:
"schema.PKG_TEST"
ORA-06512: at line 4


SQL> r
  1  declare
  2  retval char(1);
  3  begin
  4  retval := pkg_test.fnc_test;
  5  dbms_output.put_line(retval);
  6* end;

PL/SQL procedure successfully completed.

So if you see in the 11g run, the first run went fine (behaviour same as 10g). During the second run, we get the set of errors indicating that the package is invalid. Somehow the 3rd time, it did not error out. 

This is the difference I am trying to understand. 

I also tried to determine the status of the package. What is happening is in 10g, the status of the package stays VALID

select object_name, object_type, status from user_objects where object_name like '%PKG_TEST%'

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
PKG_TEST
PACKAGE             VALID

PKG_TEST
PACKAGE BODY        VALID

But in 11g the status of the package is always INVALID. 

 select object_name, object_type, status from user_objects where object_name like '%PKG_TEST%'

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         STATUS
------------------- -------
PKG_TEST
PACKAGE BODY        INVALID

PKG_TEST
PACKAGE             VALID

We started off with the same set of code in both the environments. Not sure why this difference of behaviour between 10g and 11g. Is there some known parameter that would cause this ?

Tom Kyte

Followup  

August 13, 2011 - 4:07 pm UTC

you will need to give me the step by steps to reproduce - code and all

make it small, remove anything NOT RELEVANT TO REPRODUCING THE ISSUE

invalid procedure

February 24, 2012 - 11:01 pm UTC

Reviewer: A reader

Tom:

I have a static HTML form that submits search criteria to a pl/sql form handler (procedure B) using mod_plsql.

I keep getting issues with "invalid" procedure with "B" whenever I change something in the DB.

Is it because it is a procedure? I thought when you call it should "auto recompile" when it is run.

Would the issue be resolved if I create a package?


<<<
Don't use standalone procedures! Use packages. Packages break the dependency chain.

If procedure A calls procedure B and B is "changed", then A is directly affected and must
be recompiled. There is no "auto recompile A when B changes" option -- although A will
recompile itself automatically the next time it is run.
>>>

Tom Kyte

Followup  

February 25, 2012 - 11:57 pm UTC

need more information.

"I keep getting issues with "invalid" procedure with "B" whenever I change
something in the DB."

what are your issues exactly. I have issues with my car whenever I drive it - can you help me?

compile

February 27, 2012 - 10:57 am UTC

Reviewer: A reader

Tom:

I will send you a brand new car! Which brand do you like.

What I am saying is that I have a "STATIC HTML" form on a web server 1 which posts data to a plsql form handler (another server 2).

When I change something in the database where the form handler is it becomes "invalid" and when someone uses the statis form to submit a search they get

Mon, 27 Feb 2012 16:38:36 GMT
search_address: PROCEDURE DOESN'T EXIST

  DAD name: mmmm
  PROCEDURE  : search_address
  URL        : http://myserver.xxx.xx/mmmm/search_address


After I do this it works fine
SQL> alter procedure search_address compile;

so why it is not doing auto-compile when you run it?

Would creating a package that includes this form handler resolves this issue?

Tom Kyte

Followup  

February 28, 2012 - 7:19 am UTC

Sam,

You don't know my requirements, what a silly idea to put forth. All I needed was some gas. If that is your approach to debugging and fixing things, well, I don't know.


Look in your server logs for the actual error message.

Compile with Debug

September 12, 2012 - 1:54 am UTC

Reviewer: A reader

is there any overhead for compiling packages with debug info. Ofcourse during compilation there will be but after compilation is there any difference between these.

Can we use this as routine to compile packages with debug info on production.
Tom Kyte

Followup  

September 14, 2012 - 4:36 pm UTC

there is a little overhead, there is more code emitted - a little more runtime processing.

I would suggest bench marking your code, get the numbers with and without debug. that way you'll know for sure.

ramdom ora-04068

October 10, 2012 - 8:19 am UTC

Reviewer: A reader

Dear Tom,

We have a 400 users .Net application which connects to an oracle database (DB1 10.2.0.4) and calls packages belonging to this DB1.

There is also a possibility for the end user of that application to connect to another database (DB2) using a specific user/password and calls a package belonging to DB2.

5 days ago, the DB2.package has been changed and released into DB2 database. Since then (it means since 5 days) users of DB1 are experiencing the following error
ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "DB2.ZZZZ" has 
been invalidated ORA-04065: not executed, altered or dropped package body ""DB2.ZZZZ"" ORA-06508: 
PL/SQL: could not find program unit being called: ""DB2.ZZZZ"" ORA-06512: at line 1 


The only solution we have to do is to recompile the DB2.package until another user is again faced to the same error, create an incident and ask us to recompile the package and so on. We have been in such a situation for 5 days. Every days we recompile this DB2.package. I have searched a lot but no clue up to now.

Could you please help me find the solution for this

Thanks in advance
Tom Kyte

Followup  

October 10, 2012 - 9:05 am UTC

recompiling the package is the root cause of this error!


basically, you have people connected to the database - they ran some code, including this DB2 package. This DB2 package must have global variables (it maintains a state). You recompile that package and it wipes out their program state. the next time they run their code that tries to access the DB2 package - we *have* to tell you "hey, the global variables you set are wiped out, gone - your existing state is DONE - over - kaput". You then make the decision on whether it is safe to proceed or not in your code.

All you need do is call the package again in that session.


In 11g you'll be able to use edition based redefinition to avoid this issue altogether. The existing connections can continue to use the "old" DB2 package - new connections can use the new DB2 package (if that makes sense to do so)

otherwise, if you overwrite some code and wipe out a program state - we sort of have to tell you - and you decide whether it is safe to continue or not.

or, implement the DB2 package *without* a state. get rid of global variables.

October 12, 2012 - 1:41 am UTC

Reviewer: A reader

Thanks for your answer.

We stopped recompiling this DB2.package. But end users were complaining more and more about that error. So we decided to use the brut force (a) drop DB2.package, (b) drop DB2.package synonym (c) recreate package and synonym and grants

Since then, the problem has definitely been solved.

Best regards

_disable_fast_validate

August 12, 2013 - 7:13 pm UTC

Reviewer: Ravi B from Bay Area,CA

Hi Tom,

We have a product which we ship to customers, part of which, has an ETL component which could potentially deal with several million rows per ETL (usually large datasets).

As part of ETL we

1) disable primary keys and foreign keys
2) drop the indexes
3) perform ETL
4) enable primary keys and foreign keys
5) recreate the indexes

This was working pretty well for past several years without any complaints from our customers.

Recently one of our new customer started complaining that the whole ETL process fails with error ORA-04068: EXISTING STATE OF PACKAGES HAS BEEN DISCARDED.
I spent several hours with the customer but couldn't figure out the problem not reproduce this on our local 11gR2 instances.

Following the process flow.

MAIN_PROCEDURE
1) disable PK
2) disable FK
3) execute PROCEDURE_P1
4) execute PROCEDURE_P2 <-- FAILS AT THIS POINT
5) enable PK
6) enable FK

At this point PROCEDURE_P1 and PROCEDURE_P2 are both VALID.
MAIN_PROCEDURE is invalid.

The error is:

ORA-04068: existing state of packages has been discarded ORA-04065: not executed, altered or dropped stored procedure "PROCEDURE_P2" ORA-06508: PL/SQL:
could not find program unit being called: "PROCEDURE_P2" ORA-06512: at "MAIN_PROCEDURE", line 190 ORA-06512: at line 1


I could not reproduce this error on our local machines with the same data on 11gR2.
After researching on internet i found similar issues with few people. This error occurs only 11.2.0.3.0 on RAC which our client has.

We filed a SR with oracle.
Oracle support told us that this is a known issue and they think it is not a bug but a "feature" that was recently introduced.

In their exact words

"This is not an issue on your 11.2.0.2 (Non-RAC instance) because the implicit validation of PROCEDURE_P2 proceeds via the newly introduced fast-validation mechanism (_disable_fast_validate=TRUE) of PL/SQL. Due to this, PROCEDURE_P2 is set to a valid status without an actual compilation happening.
But in RAC environment, fast validation will not be possible and so compilation forcefully occurs internally through ALTER.. COMPILE statement, which makes the object invalid"

The following has been recommended

1) use "KEEP INDEX" while disabling primary keys
2) use an exception block to trap ORA-04068 and reexecute
same procedure which would auto compile
3) use alter session set events
'10624 trace name context forever, level 1'

The first solution worked. But might have performance impact by keeping index.

Second solution might be too expensive because we have to re-execute the same procedure twice. Not an elegant solution.

Third solution worked but not sure what could be side effects. I am dynamically setting alter session at the beginning of stored procedure.

1) Since this is a product that we ship to a customer, could i safely set this trace without upsetting the local DBA's?
2) do I need any special privileges to set this trace at session level?
3) what is the meaning of this trace?

Please let me know your thoughts on this.

Thanks!

Tom Kyte

Followup  

August 12, 2013 - 8:10 pm UTC

some questions for you

a) does procedure p2 have global variables? should it have global variables?

b) what if you add a step 2.5, create a new session (logout, logon)



you'd need to have alter session granted to you to enable that trace setting. Please utilize support to get information about the safety and side effects of using a trace event like that - I would be against it in general as the trace events have, can and will change in meaning over releases.

_disable_fast_validate

August 12, 2013 - 8:40 pm UTC

Reviewer: Ravi B from Bay Area,CA

No. P2 doesn't have global variables.
Not sure how do i logout/login in a stored procedure. Are you suggesting i use DBMS_JOB or AUTONOMOUS TRANSACTION?
Tom Kyte

Followup  

August 12, 2013 - 10:00 pm UTC

i thought steps 1,2,3,4... were discrete steps by a client. if they are in a procedure - you cannot log out.



if p2 doesn't have globals and doesn't touch anything that has globals - the ora error would seem to me to be "incorrect" - there isn't any package state to be discarded.


_disable_fast_validate

August 12, 2013 - 8:46 pm UTC

Reviewer: Ravi B from Bay Area,CA

Update from support:

Event 10624 can be set at level 1 in the session to prevent this issue.
Then the drop index will not invalidate instead it will write a message to the alert.log due to the event being set.

Since you are setting this event at session level this will not impact other applications.


Package subprogram dependency

November 18, 2013 - 3:18 pm UTC

Reviewer: Prajjwal Mallik from Kolkata, India

Hi Tom,
How easy or difficult is it to find the dependent objects of a single package subprogram (e.g. a procedure within package) alone?

Regards,
Prajjwal

Can't compile procedure from within itself

March 04, 2017 - 1:28 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Hello Tom,

This might have already been answered by you in the first question on this page.. but I wasn't able to correlate this with my current query. Could you please provide your input on this specific situation? Sorry if it is a repeat of the same scenario in that first question...

I have one PROCEDURE (proc_tab_select) that does a lot of SELECTs on a lot of tables. I have created another procedure now (proc_tab_refresh), that is able to do TRUNCATE and INSERT INTO on all the tables that the proc_tab_select is using. This insert happens from a remote source using dblink, just fyi.
I wanted to call proc_tab_refresh at the beginning of proc_tab_select, so that all tables that it is using, get truncated and refreshed with the latest data, before it starts doing the SELECTs on it.
The problem is that, as proc_tab_refresh truncates all tables and refreshes them, the proc_tab_select gets invalidated. I cannot compile a procedure from within the PROCEDURE itself.
I tried creating a third PROCEDURE (proc_final) that will call proc_tab_refresh, then do the alter procedure proc_tab_select compile, then will call proc_tab_select. But that doesn't seem to work too.

Should I be creating packages to be able to achieve this? Could you please guide me on this? I just want the sequence to be truncate tables first, then compile the invalidated procedure, and then run the select procedure.

Thanks very much !
Connor McDonald

Followup  

March 04, 2017 - 2:07 am UTC

I'm confused - a truncate should not make a procedure invalid, eg

SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> create or replace
  2  procedure P is
  3  begin
  4    execute immediate 'truncate table t';
  5
  6    for i in ( select * from t )
  7    loop
  8      null;
  9    end loop;
 10  end;
 11  /

Procedure created.

SQL>
SQL>
SQL> select status from user_objects where object_name = 'P';

STATUS
-------
VALID

1 row selected.

SQL>
SQL> exec P;

PL/SQL procedure successfully completed.

SQL>
SQL> select status from user_objects where object_name = 'P';

STATUS
-------
VALID



That said, packages is still the way to go

Can't compile procedure from within itself

March 04, 2017 - 8:43 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

I tried the commands you provided, and indeed, the procedure doesn't get invalidated. But I'm not sure what exactly causes my procedure to get invalidated every time.
If I run proc_tab_select and proc_tab_refresh individually (separately), they both run fine without any errors. But if I call proc_tab_refresh before proc_tab_select in the same procedure, then it fails with the error:

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure
"...<name_removed>"
ORA-06508: PL/SQL: could not find program unit being called:
"...<name_removed>"
ORA-06512: at "...<name_removed>", line 27
ORA-06512: at line 1

At that time, when I check the state of proc_tab_select, it is invalid. All proc_tab_refresh does is, disabling triggers, disabling constraints, make indexes unusable, drop unique index, truncate table, insert data into the table, rebuild indexes, re-enable constraints, re-create the unique indexes, and re-enable triggers.
I can try that using package also. Could you please tell me how I should implement this using packages?
Connor McDonald

Followup  

March 05, 2017 - 12:31 am UTC

ORA-04068: existing state of packages has been discarded

That is not a compilation error, that is a warning about *state*.

https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:3516589324948

Can't compile procedure from within itself

March 05, 2017 - 3:43 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Ah ok. But the procedure exits out with that message, and since I have to re-compile it to make it a valid state again, I understood that proc_tab_refresh is somehow causing proc_tab_select to become invalidated due to the state changes of trigger/constraints and rebuilding of table and indexes.

proc_tab-refresh, as I mentioned earlier, is disabling triggers, disabling constraints, making indexes unusable, dropping unique index, truncating table, inserting data into the table, rebuilding indexes, re-enabling constraints, re-creating the unique indexes, and re-enabling triggers. For each table that proc_tab_select is going to SELECT from, in the next stage.

So:
- Would it be possible to keep the existing state from changing, so that the procedure completes successfully? I check in dba_dependencies, and it shows dependency on proc_tab-refresh, and on some of the procedures,tables,synonyms,packages,links that are called within proc_tab_select itself, except for SYS_STUB_FOR_PURITY_ANALYSIS package from SYS (that I don't know where its being used). Since all dependencies are related to objects that will be used AFTER proc_tab-refresh completes, I think it is proc_tab-refresh that is causing it to change its state and become invalidated, and thus exiting out of the procedure.

- Would you advice me to handle the INVALID state of proc_tab_select in some exception handler, and to make the procedure go ahead? If yes, could you tell me what exactly I should have to specify in the handler to look for?

- Could you also tell me about how I can make it work using packages, if that's the best and recommended way to do it?
Connor McDonald

Followup  

March 07, 2017 - 1:54 am UTC

"since I have to re-compile it to make it a valid state again"

No you dont. You just have to re-run it.

--
-- Session 1
--
SQL> create or replace
  2  package PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace
  2  package body PKG is
  3    my_var varchar2(20);
  4    procedure P is
  5    begin
  6      my_var := 'Blah';
  7    end;
  8  end;
  9  /

Package body created.

--
-- Session 2
--
SQL> exec pkg.p

PL/SQL procedure successfully completed.

--
-- Session 1 changes the code
--


SQL> create or replace
  2  package body PKG is
  3    my_var varchar2(20);
  4    procedure P is
  5    begin
  6      my_var := 'Other blah';
  7    end;
  8  end;
  9  /

Package body created.

--
-- Session 2 will get ora-4068 on next execution
--


SQL> exec pkg.p
BEGIN pkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1

--
-- but after that, will be fine
--
SQL> exec pkg.p

PL/SQL procedure successfully completed.


Triggers don't observe double tap rules

February 22, 2019 - 10:52 pm UTC

Reviewer: Brian

I'm finding that a trigger that references a package cannot recover from a package state change due to a recompile. It will forever fail until a DBMS_SESSION.RESET_PACKAGE is issued or the session is dropped and recreated.

The problem with doing a reset_package (or either) is that the damage is already done and the trigger catastrophically failed! There seems to be no way to handle this which. I've tried everything I can think of. Any advise?
Connor McDonald

Followup  

February 23, 2019 - 6:35 am UTC

I need to see your full test script - I'm not seeing that.

--
-- Session 1
--
SQL> create or replace
  2  package pkg is
  3    g date := sysdate;
  4    procedure p;
  5  end;
  6  /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3    procedure p is
  4      x date;
  5    begin
  6      x := g;
  7    end;
  8
  9  end;
 10  /

Package body created.

SQL>
SQL>
SQL>
SQL> create table t ( x int );

Table created.


SQL> create or replace
  2  trigger trg
  3  before insert on t
  4  for each row
  5  begin
  6    pkg.p;
  7  end;
  8  /

Trigger created.

SQL> insert into t values (1);

1 row created.

--
-- Session 2
--
SQL> create or replace
  2  package body pkg is
  3    procedure p is
  4      x date;
  5    begin
  6      x := g+1;
  7    end;
  8
  9  end;
 10  /

Package body created.

--
-- Session 1
--
SQL> insert into t values (1);
insert into t values (1)
            *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at "MCDONAC.TRG", line 2
ORA-04088: error during execution of trigger 'MCDONAC.TRG'


SQL> insert into t values (1);

1 row created.


told 'em (millenium hand and shrimp)

February 25, 2019 - 7:06 am UTC

Reviewer: Racer I.

Hi,

The "seems never to heal" phenomenon is well known in middle tier architectures, i.e. connection pool connections never heal on their own. I believe this is because the driver assumes that no human sees the ORA-04068/1/5-warnings (not errors) so never clears the "I warned them, they didn't listen" flag.
We've also beaten our heads on this for a long time but haven't come up with a clean solution. So we have rigid organizational barriers (rollout-windows, patch-procedures) to minimize the risk. Then manual pool refresh/server restarts (staggered) if necessary.

regards,
Connor McDonald

Followup  

February 26, 2019 - 2:33 am UTC

"I believe this is because the driver assumes that no human sees the ORA-04068"

Not sure if I buy this :-) One reason that it seems to be "repeating" in connection pool environments is due to:

- state is discarded
- app requests a session from the pool, gets (say) session #1
- then gets ora-4068
- app requests a session from the pool, gets (say) session #7
- then gets ora-4068 (because first call from session 7)
- app requests a session from the pool, gets (say) session #3
- then gets ora-4068 (because first call from session 3)

etc


Double Tap - Trigger Demo

February 25, 2019 - 2:56 pm UTC

Reviewer: A reader

I am trying to catch the exception in the trigger because I want to handle it and continue. But, even handling it and executing it twice still results in an errror.
-----------------------------------------------------------

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
Connected as E11775@test

SQL>
SQL> create or replace package TestMe is
2 gGlobalVarTest number := 1;
3
4 procedure test(a number);
5 end TestMe;
6 /

Package created


SQL>
SQL> create or replace package body TestMe is
2 Procedure test(a number) is
3 begin
4 gGlobalVarTest := 2;
5 end test;
6 begin
7 gGlobalVarTest := 6;
8 end TestMe;
9 /

Package body created


SQL>
SQL> create or replace trigger tstTrigger
2 before insert or update or delete
3 on test
4 for each row
5 begin
6 TestMe.test(0);
7 exception when others then
8 TestMe.test(0);
9 end tstTrigger;
10 /

Trigger created

SQL> insert into test values(to_char(sysdate, 'yyyy'));

1 row inserted


SQL>
SQL> create or replace package body TestMe is
2 Procedure test(a number) is
3 begin
4 gGlobalVarTest := 2;
5 end test;
6 begin
7 gGlobalVarTest := 7;
8 end TestMe;
9 /

Package body created

SQL> insert into test values(to_char(sysdate, 'yyyy'));
insert into test values(to_char(sysdate, 'yyyy'))

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "E11775.TESTME" has been invalidated
ORA-04065: not executed, altered or dropped package body "E11775.TESTME"
ORA-06508: PL/SQL: could not find program unit being called: "E11775.TESTME"
ORA-06512: at "E11775.TSTTRIGGER", line 4
ORA-04061: existing state of package body "E11775.TESTME" has been invalidated
ORA-04065: not executed, altered or dropped package body "E11775.TESTME"
ORA-06508: PL/SQL: could not find program unit being called: "E11775.TESTME"
ORA-04088: error during execution of trigger 'E11775.TSTTRIGGER'

SQL> insert into test values(to_char(sysdate, 'yyyy'));

1 row inserted

Connor McDonald

Followup  

February 26, 2019 - 3:04 am UTC

You *have* to let the calling application know that the state is gone. Otherwise you're running the risk of a data corruption amongst other things.

An example of that here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=ora-4068#1971326400346443356

Clarification

February 25, 2019 - 3:37 pm UTC

Reviewer: A reader

Just to make myself clear, I cannot handle the invalid state of package scenario in the application doing the insert because we don't own that application.

What I do want to do is handle the invalid state of package in the trigger itself. It seems like I should be able to but the behavior is not consistent with the double tap rules.

Removing global variables from the package is also not an option. Again, I need to handle it in the trigger.

February 26, 2019 - 2:39 pm UTC

Reviewer: A reader

In my case, the calling application is the trigger. The trigger is calling the package and I want to be able to handle package state errors there.

Triggers are written in PL/SQL and have exception handlers, hence it is a program. I want the trigger which is a program to be able to decide whether or not to continue after a package state loss.

Why can't my program, i.e. trigger intercept a package state error and handle it?

I hear over and over again that package state errors are not really errors and are informative. But the way it is implemented (by oracle) throws it as an error. Errors interrupt program execution. Errors can typically be handled. I want to handle package state errors in my trigger. Please tell me how to handle package state errors in my trigger.

It should be my choice whether or not to continue after a package state error in a calling trigger. Not Oracle's.
Connor McDonald

Followup  

February 28, 2019 - 3:09 am UTC

I hear over and over again that package state errors are not really errors and are informative


I don't agree with that statement (I'm not criticising you, I'm criticising people that have published statements of that kind). For me, it *is* an error because you attempted to reference state that no longer exists.

If the state is of no use to you, then the trigger could call dbms_session.modify_package_state. That is you saying "I dont care about the state" (and dealing with all the issues that this carries along with it).

February 28, 2019 - 6:55 pm UTC

Reviewer: A reader

I tried that and the trigger still fails.
dbms_session.modify_package_state does not take affect until after the PL/SQL unit is done executing. So, trying to repeat the package action after executing dbms_session.modify_package_state still errors with invalid package state.

it just seems like this would be so much easier if I had the choice to ignore the invalid state because in this case I know the state is irrelevant. Not all procedures in the package depends on the global variables or may just re-init them on execution.

I guess at this point our options are to:
1) plan outages so we can flush all the connection pools on our servers.
2) Look into EBR.
3) Refactor the package to stop using global variables.

Connor McDonald

Followup  

March 01, 2019 - 1:21 am UTC

Example - without dbms_session

SQL> create or replace
  2  package pkg is
  3    g int;
  4    procedure p;
  5  end;
  6   /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3    procedure p is
  4      x int;
  5    begin
  6      g := g + 1;
  7    end;
  8  begin
  9    g := 0;
 10  end;
 11   /

Package body created.

SQL>
SQL> create table t ( x int, something_that_uses_the_global int );

Table created.

SQL>
SQL> create or replace
  2  trigger trg
  3  before insert on t
  4  for each row
  5  begin
  6    pkg.p;
  7    :new.something_that_uses_the_global := pkg.g;
  8  end;
  9  /

Trigger created.

SQL>
SQL> insert into t (x) values (111);

1 row created.

SQL> insert into t (x) values (222);

1 row created.

SQL> insert into t (x) values (333);

1 row created.

SQL> select * from t;

         X SOMETHING_THAT_USES_THE_GLOBAL
---------- ------------------------------
       111                              1
       222                              2
       333                              3

3 rows selected.

--
-- (here, I altered the package in another session, and then came back to this session)
--

SQL> insert into t (x) values (444);
insert into t (x) values (444)
            *
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at "MCDONAC.TRG", line 2
ORA-04088: error during execution of trigger 'MCDONAC.TRG'


SQL> insert into t (x) values (555);

1 row created.

SQL> select * from t;

         X SOMETHING_THAT_USES_THE_GLOBAL
---------- ------------------------------
       111                              1
       222                              2
       333                              3
       555                              1

4 rows selected.

SQL>
SQL>
SQL>


And here is the same with the reset of state

SQL> create or replace
  2  package pkg is
  3    g int;
  4    procedure p;
  5  end;
  6   /

Package created.

SQL>
SQL> create or replace
  2  package body pkg is
  3    procedure p is
  4      x int;
  5    begin
  6      g := g + 1;
  7    end;
  8  begin
  9    g := 0;
 10  end;
 11   /

Package body created.

SQL>
SQL> create table t ( x int, something_that_uses_the_global int );

Table created.

SQL>
SQL> create or replace
  2  trigger trg
  3  before insert on t
  4  for each row
  5  begin
  6    pkg.p;
  7    :new.something_that_uses_the_global := pkg.g;
  8    dbms_session.modify_package_state(dbms_session.reinitialize);
  9  end;
 10  /

Trigger created.

SQL>
SQL> insert into t (x) values (111);

1 row created.

SQL> insert into t (x) values (222);

1 row created.

SQL> insert into t (x) values (333);

1 row created.

SQL> select * from t;

         X SOMETHING_THAT_USES_THE_GLOBAL
---------- ------------------------------
       111                              1
       222                              1
       333                              1

3 rows selected.

--
-- (here, I altered the package in another session, and then came back to this session)
--

SQL>
SQL>
SQL> insert into t (x) values (444);

1 row created.

SQL> insert into t (x) values (555);

1 row created.

SQL> select * from t;

         X SOMETHING_THAT_USES_THE_GLOBAL
---------- ------------------------------
       111                              1
       222                              1
       333                              1
       444                              1
       555                              1

5 rows selected.

SQL>
SQL>


So no error...but you do *not* have the state retained across calls. In effect you are promising that you don't need the state *and* that nothing else in your session needed the state or will need it again.

not now, can't you see I'm busy

March 01, 2019 - 7:21 am UTC

Reviewer: Racer I.

Hi,

Ouch. That is harsh (but kinda expected). This clears the state (of every package in the session) after every call (even all the successful ones) instead of just clearing it on encountering a specific error. With no state to destroy the package change in the other session has no effect on this one anymore.
Putting the call in an EXEPTION-branch won't work because of the "takes effect after the PL/SQL call that made the current invocation finishes running".

https://community.oracle.com/thread/4159650
mentions mod_plsql, which does this at the end of every transaction.
Similar to declaring all your pl/sql-code as SERIALLY-REUSABLE (which makes them permanently amnesic, a horribly thought) but apparently Oracle disallows calls to such packages from triggers. Presumeably for a reason (bye bye consistency) so I guess this workaround falls into the same don't-go-there category.

If pushed into that corner I would try something asynchronous via DBMS_JOB (until DBMS_SCHEDULE is "fixed" to not use autonomous transaction) to do the calls that may fail and use the AQ-retry mechanisms.

regards,
Connor McDonald

Followup  

March 06, 2019 - 6:47 am UTC

Ouch indeed.

But then again, there's a certain contradiction when people say:

"Just reset the state, I don't care"

then followed up with:

"OMG!!!! You reset the state!!!!!!"

:-)

peek-a-boo

March 04, 2019 - 7:19 am UTC

Reviewer: Racer I.

Hi,

A little more rambling :
> middle tier connection pool
http://dbj2ee.blogspot.com/2007/10/dealing-with-oracle-plsql-error-ora.html
> also acknowledge Tom Kyte, discussions with whom led to my not recommending solution 4 after all.

This shows that it's not the driver second-guessing human involvement, so our servers should have "healed" after X (=pool size) exceptions each. We just never waited that long.

So my next guess how Oracle handles these :
- Session A/B/C compiles/loads some accessed packages (including, say, package X) (directly or indirectly) leaving session-state (Oracle is getting better with each release in telling what is state, CONSTANTS no longer are, for example)
- Session Z recompiles package X, this removes that package and its state from all sessions, that had it loaded, leaving behind a (grave- ;) marker.
- Session A (say) needs X again (directly or indirectly). When loading X it notices the marker -> ORA-0406x. It clears the marker but :
"takes effect after the PL/SQL call that made the current invocation finishes running".
So no double-tap inside PL/SQL.

Suggestion for an improvement from Oracle :
-new method DBMS_SESSION.HAS_RECOMPILED_PACKAGES, which returns YES if there are any markers in this session.
Than you can sanitize a connection with a (custom) validation call (when retrieving a connection from the pool) which does
IF (DBMS_SESSION.HAS_RECOMPILED_PACKAGES) THEN
dbms_session.modify_package_state(dbms_session.reinitialize)
END IF;

This should be quite optimal :
- doesn't discard all packages state if not necessary
- doesn't discard just some state but cleans all, so no inconsistencies
- doesn't try to clean the session in the middle of a transaction, only at the beginning
- doesn't require to actually access one of the marked packages (or know which they are)
- doesn't check too much (a DB can have thousands of packages, but a specific server will only use about say 100 or so (across all possible calls/uses cases) because its an online-server say and the others are used by process-servers or by sqlplus-sessions only.
- doesn't introduce tempting ways to shoot yourself in the foot with
GET_RECOMPILED_PACKAGE_COUNT, IS_PACKAGE_RECOMPILED('X') or CLEAR_RECOMPILED_STATE[('X')]...

Connor McDonald

Followup  

March 06, 2019 - 6:53 am UTC

That seems a lot of management when moving stateful variables into their own package mitigates almost all of these issues.

oracle is all about persisting state

March 06, 2019 - 10:09 am UTC

Reviewer: Racer I.

Hi,

> moving stateful variables into their own package

Fair point but still lots of philosophical and technical questions pop up with that approach. First of course this is fortunately not a widely occuring problem (just annoying when you do run into it). So not much at stake.

There might be a big legacy application that is not easily refactored. Or you would need to be aware of this when starting a new app and make everyone adhere to this principle. Which may seem weird, because the code as such doesn't need this complication.

It makes all state kinda public (even if you hide it in the state-packages body you would need spec-functions to access/manipulate it) but you can use ACCESSIBLE BY to hide it again I guess.

Would you suggest a state-package per code-package or a big package holding many packages' state or maybe multiple state-packages per code-package in case they do need to change and you want to minimize the impact?

What happens if the code package declares a type (RECORD say) and the state package has variables of that type? Will recompiling the code package affect the state package too (even if the record structure does not change)? I guess then types should be co-located with their variables in the state packages and hopefully not change to often.

Maybe Oracle could move a packages' state automagically in a (hidden) state-package (handling access and such transparently)? Similar to how package-types have hidden SQL-types generated for them (yeah for %ROWTYPE)? Presumably there are a bunch of rules by which Oracle can tell if the new code can bind to the old state seamlessly (no ORA-0406*) or if the state needs to be reset (hopefully rarely). Such rules must exist for manual state-packages already.

Of course if the meaning (but not structure) of the state changes drastically with the new code you could still shoot yourself in the foot but that applies to manual state packages too.

regards,
Connor McDonald

Followup  

March 06, 2019 - 11:10 am UTC

Would you suggest a state-package per code-package or a big package holding many packages' state or maybe multiple state-packages per code-package in case they do need to change and you want to minimize the impact?


My general modus operandi is for each package "PKG" that requires some globals/state etc, then I'll have a partner package called "PKG_G" which contains the globals.

I've seen *many* instances of people having a package called "GLOBALS" or "COMMON" and dumping everything in there. I'm definitely not a fan of that, because pretty much any change is going to crunch the whole application.