Harshan, April 16, 2001 - 9:27 pm UTC
Helena Markova, July 02, 2001 - 2:39 am UTC
Well explained
Kavitha, July 17, 2001 - 10:13 am UTC
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!
Georgi Mechev, September 14, 2001 - 10:33 am UTC
procedures or packages
ajay, September 25, 2001 - 8:12 pm UTC
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
Paulo Motta, June 26, 2002 - 1:04 pm UTC
Does anyone have an additional bibliografy about this stuff ?
Thanks.
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
Jan, June 26, 2002 - 9:40 pm UTC
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,
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
Anil, October 30, 2002 - 2:58 am UTC
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
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
Thiyagarajan, October 31, 2002 - 10:10 am UTC
amusing yourself
asktom fanatic, October 31, 2002 - 6:06 pm UTC
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?
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
cachitok@yahoo.com, November 04, 2002 - 6:51 pm UTC
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
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.
cachitok@yahoo.com, November 05, 2002 - 10:46 am UTC
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?
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
Jeremy Smith, November 05, 2002 - 4:23 pm UTC
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.
cachitok@yahoo.com, November 05, 2002 - 5:00 pm UTC
Oh, about that....
Really Sorry, I misunderstand what I read in the documentation,
This paragraph,
from "Application Developers 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
Robert, December 02, 2002 - 10:46 am UTC
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
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....
Robert, December 02, 2002 - 2:26 pm UTC
>> 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
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
Steve Booth, December 02, 2002 - 4:36 pm UTC
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
December 02, 2002 - 5:25 pm UTC
got an example?
packages
mo, January 03, 2003 - 10:01 am UTC
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,
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
sean, January 06, 2003 - 7:27 pm UTC
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
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
cachitok@yahoo.com, January 07, 2003 - 3:01 pm UTC
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
Ashok Shinde, March 14, 2003 - 5:02 am UTC
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.
March 14, 2003 - 5:59 pm UTC
look in dba_dependencies, see what it is dependent on.
State of the package when auto compile
A reader, August 29, 2003 - 10:44 am UTC
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,
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:
A reader, August 29, 2003 - 11:15 am UTC
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.
August 29, 2003 - 12:23 pm UTC
so, the package didn't have a state yet? no problem.
RE: Alter table add partition...
Mark A. Williams, August 29, 2003 - 12:15 pm UTC
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?
A reader, August 29, 2003 - 1:34 pm UTC
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,
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
A reader, October 17, 2003 - 5:58 pm UTC
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.
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!
A reader, October 20, 2003 - 1:55 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."
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?
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!!
A reader, October 20, 2003 - 3:58 pm UTC
In 9i Release 2 : Removes double invalidations ??
Raj.N, October 20, 2003 - 10:07 pm UTC
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."
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?
Dan Loomis, October 27, 2003 - 1:56 pm UTC
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.
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
Henry, October 27, 2003 - 4:07 pm UTC
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
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
Robert, November 30, 2003 - 4:21 pm UTC
Is there a way to find out what tables/views a package references ?
Thanks
November 30, 2003 - 7:44 pm UTC
user|all|dba_dependencies
(a view)
How many packages
B, December 16, 2003 - 2:11 pm UTC
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!
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
B, December 16, 2003 - 2:53 pm UTC
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.
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
Dale Ogilvie, January 25, 2004 - 11:40 pm UTC
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
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
Dale Ogilvie, January 26, 2004 - 3:38 pm UTC
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
Pushparaj Arulappan, March 09, 2004 - 4:00 pm UTC
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
March 09, 2004 - 10:25 pm UTC
whats the goal? (eg: why?)
in short, no, but what are you trying to do?
A reader, March 25, 2004 - 3:07 pm UTC
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?
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..
Mohini, May 11, 2004 - 2:12 pm UTC
--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.
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
mohini, May 11, 2004 - 4:02 pm UTC
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.
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
mohini, May 12, 2004 - 9:09 am UTC
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
May 12, 2004 - 6:46 pm UTC
not following your thread here.
what is happening that is forcing you to recompile a spec.
mohini, May 13, 2004 - 9:16 am UTC
--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....
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
JB, May 24, 2004 - 2:08 pm UTC
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;
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
Ilya, June 10, 2004 - 9:59 am UTC
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
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
A reader, July 11, 2004 - 12:37 pm UTC
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!:)
July 11, 2004 - 1:35 pm UTC
client side = forms
server side = stored procedure.
thanx!
A reader, July 11, 2004 - 1:42 pm UTC
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)
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!
A reader, July 12, 2004 - 11:21 am UTC
Can dropping an index invalidate a view
Logan Palanisamy, September 20, 2004 - 2:39 pm UTC
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.
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
Susan, September 23, 2004 - 4:24 pm UTC
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.
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
A reader, October 10, 2004 - 10:06 pm UTC
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
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
Thiru, October 15, 2004 - 12:40 pm UTC
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
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!!
Reader, October 15, 2004 - 3:50 pm UTC
Awesome explanaton. Crystal clear.
Great!
Ashim Chakrabarty, November 16, 2004 - 5:06 am UTC
One more reason to use packages!
Let two procedures run in parallel
Sean, March 10, 2005 - 8:56 pm UTC
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.
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
avitos@hotmail.com, March 16, 2005 - 5:38 am UTC
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
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.
Cefers.br, March 17, 2005 - 8:43 am UTC
>>>
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.
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
Cefers.br, March 17, 2005 - 9:57 am UTC
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
Cefers.br, March 21, 2005 - 9:42 am UTC
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
Shalu, April 01, 2005 - 10:29 am UTC
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
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
soji ademeji, April 20, 2005 - 3:26 pm UTC
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.
April 20, 2005 - 9:14 pm UTC
does not compute
performance issue
Ravi Kumar, July 14, 2005 - 7:04 am UTC
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..
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.....
Prashant, September 11, 2005 - 3:10 am UTC
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
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, ...
Alberto Dell'Era, September 11, 2005 - 9:33 am UTC
> 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.
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
Prashant, September 11, 2005 - 6:06 pm UTC
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
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
Prashant, September 11, 2005 - 8:57 pm UTC
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
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)
Aaron, September 13, 2005 - 12:37 pm UTC
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!
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
Aaron, September 13, 2005 - 1:55 pm UTC
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;
/
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
Aaron, October 17, 2005 - 2:19 pm UTC
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;
/
reword question
Aaron, October 17, 2005 - 3:05 pm UTC
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 . . .
October 17, 2005 - 8:56 pm UTC
that syntax is not supported....
sorry - it just doesn't work that way.
Thanks
Aaron, October 18, 2005 - 10:10 am UTC
Well, Thanks for the effort anyhow . . .
recompiling just the package body in production
Menon, November 17, 2005 - 1:39 pm UTC
"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?
November 18, 2005 - 9:52 am UTC
It is not normal to compile code that is being used.
That is not normal.
sure..
Menon, November 18, 2005 - 10:32 am UTC
"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!
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
Menon, November 18, 2005 - 5:06 pm UTC
but this happens even if the state is a global constant?
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
A reader, November 21, 2005 - 8:43 am UTC
Hi Tom,
How to emulate the command
"SQL> create or replace TABLE "
November 21, 2005 - 8:54 am UTC
give me the possible use case?
yes
A reader, November 21, 2005 - 9:50 am UTC
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"
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
A reader, November 21, 2005 - 12:41 pm UTC
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??
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
Marcio Portes, November 21, 2005 - 2:08 pm UTC
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;
/
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
Marcio Portes, November 21, 2005 - 2:31 pm UTC
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
A reader, November 21, 2005 - 2:45 pm UTC
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
A reader, November 28, 2005 - 10:05 pm UTC
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
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
Subhasis Gangopadhyay, December 02, 2005 - 10:10 am UTC
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
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
deba, December 02, 2005 - 12:08 pm UTC
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
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!!
OWB, December 06, 2005 - 2:20 pm UTC
Thanks Tom for your wonderful insights!
Su Baba, February 21, 2006 - 1:46 pm UTC
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?
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
A reader, April 07, 2006 - 7:44 pm UTC
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.
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.
A reader, April 08, 2006 - 2:38 pm UTC
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.
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)
A reader, April 10, 2006 - 1:53 pm UTC
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.
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... )
A reader, April 11, 2006 - 12:44 pm UTC
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
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
sat, April 18, 2006 - 1:39 pm UTC
is there a fastest way to recompile the objects other than alter <object type > <object nAme > compile in each version
April 18, 2006 - 3:36 pm UTC
just let them fix themselves? I do.
Recover from partition add/drop
Randall, June 09, 2006 - 10:28 am UTC
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>
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.
Randall, June 09, 2006 - 2:34 pm UTC
"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.
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)
Randall, June 12, 2006 - 8:43 am UTC
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.
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
A reader, July 07, 2006 - 7:36 am UTC
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.
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
Scott, July 10, 2006 - 7:08 am UTC
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
July 10, 2006 - 8:32 am UTC
is this distributed? are A and B in the same database?
last_ddl_time / timestamp issue
Scott, July 10, 2006 - 10:17 am UTC
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.
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
SKL, July 11, 2006 - 2:51 am UTC
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
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
Kumar from NY, October 09, 2006 - 12:16 pm UTC
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?
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!
Kumar, October 09, 2006 - 2:07 pm UTC
Thanks Tom for the answer and the time.
what can cause massive packages invalidations
A reader, December 15, 2006 - 7:00 am UTC
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.
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?
A reader, December 15, 2006 - 6:01 pm UTC
Hi
I dont understand why grant any table to public then revoke it can cause mass invalidations?
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
prasanth, January 12, 2007 - 1:48 am UTC
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
A reader, April 12, 2007 - 9:59 am UTC
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
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
abz, May 30, 2007 - 10:15 am UTC
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.
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
abz, June 04, 2007 - 6:00 am UTC
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.
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)
A reader, October 08, 2007 - 4:36 pm UTC
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
Menon, October 16, 2007 - 9:14 pm UTC
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
Menon, October 22, 2007 - 2:55 pm UTC
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:)
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....
Menon, October 23, 2007 - 8:32 pm UTC
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!
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
Menon, October 24, 2007 - 10:06 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 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!
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
Menon, October 24, 2007 - 8:55 pm UTC
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
Menon, October 24, 2007 - 9:01 pm UTC
"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?"
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.....
Menon, October 25, 2007 - 7:45 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" "
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.
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
Menon, October 29, 2007 - 1:36 pm UTC
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.
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
Menon, October 29, 2007 - 1:41 pm UTC
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
Menon, October 29, 2007 - 2:01 pm UTC
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.
Menon, October 30, 2007 - 2:48 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!!!!)
"
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
Menon, November 04, 2007 - 9:42 pm UTC
adding procedure to an existing package
Rajat, November 27, 2007 - 4:41 am UTC
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!
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
Mike, November 28, 2007 - 7:24 am UTC
November 28, 2007 - 10:59 pm UTC
correct...
things change...
but - packages still rule.
jyothsna, August 07, 2008 - 5:09 am UTC
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
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
Mike, October 06, 2008 - 2:15 pm UTC
Is it possible to write a query to identify any sessions that are holding invalidated package states?
October 06, 2008 - 3:10 pm UTC
not that I am aware of, no
Robby, October 06, 2008 - 3:49 pm UTC
Like MS SQL Server have a partition features too but Oracle RULE. :-)
meaning in the concept guide (auto recompilation once only?)
A reader, June 23, 2009 - 1:37 pm UTC
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
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.
Fez, May 05, 2011 - 6:04 pm UTC
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?
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.
A reader, August 03, 2011 - 11:37 am UTC
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 ?
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.
A reader, August 09, 2011 - 2:03 pm UTC
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 ?
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
A reader, February 24, 2012 - 11:01 pm UTC
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.
>>>
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
A reader, February 27, 2012 - 10:57 am UTC
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?
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
A reader, September 12, 2012 - 1:54 am UTC
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.
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
A reader, October 10, 2012 - 8:19 am UTC
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
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.
A reader, October 12, 2012 - 1:41 am UTC
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
Ravi B, August 12, 2013 - 7:13 pm UTC
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!
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
Ravi B, August 12, 2013 - 8:40 pm UTC
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?
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
Ravi B, August 12, 2013 - 8:46 pm UTC
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
Prajjwal Mallik, November 18, 2013 - 3:18 pm UTC
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
DBA_without_much_developer_knowledge, March 04, 2017 - 1:28 am UTC
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 !
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
DBA_without_much_developer_knowledge, March 04, 2017 - 8:43 am UTC
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?
Can't compile procedure from within itself
DBA_without_much_developer_knowledge, March 05, 2017 - 3:43 am UTC
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?
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
Brian, February 22, 2019 - 10:52 pm UTC
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?
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)
Racer I., February 25, 2019 - 7:06 am UTC
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,
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
A reader, February 25, 2019 - 2:56 pm UTC
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
Clarification
A reader, February 25, 2019 - 3:37 pm UTC
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.
A reader, February 26, 2019 - 2:39 pm UTC
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.
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).
A reader, February 28, 2019 - 6:55 pm UTC
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.
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
Racer I., March 01, 2019 - 7:21 am UTC
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,
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
Racer I., March 04, 2019 - 7:19 am UTC
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')]...
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
Racer I., March 06, 2019 - 10:09 am UTC
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,
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.
BY YEAR
Carolina Bodor, February 18, 2020 - 12:01 pm UTC
Hi Tom
I have a question that I can't find the answer.
I need to create a function to retrieve the date for one year (12 months).
As you said before, it is different if we write 12 months and 365 days because a year could not have 365 days.
I'll write below my doubt
Select *
From table_name
Where column_date (should retrieve the date from ex: day-month-1999 to day-month-2000, being precisely 12 months ) and should change the status.
Notice that, the table has several dates and I need to make the change from all the rows that has more than 12 months.
Hope I wrote it well.
With regards
February 20, 2020 - 10:39 am UTC
Look into the ADD_MONTHS function.
If you're still stuck, submit a new question with a test case (create table + inserts) showing what you're trying to do.
A reader, February 21, 2020 - 12:04 pm UTC