-
I know that package variables will cause ORA-4068 error messages when another user tries to re-compile the package body. I know why I am getting these errors, I need to know how I can detect these errors before the user compiles a package body.
--
For example, user a creates a package:
jeff@dev817sol.us > l
1 create or replace package foo
2 is
3 function bar return varchar2;
4* end foo;
jeff@dev817sol.us > /
Package created.
Then, in the same session, the user creates the package body:
jeff@dev817sol.us > l
1 create or replace package body foo
2 is
3 v2 varchar2(80);
4 function bar return varchar2
5 is
6 ver varchar2(80);
7 begin
8 v2:='this is a package variable.';
9 ver := v2;
10 return ver;
11 end bar;
12* end foo;
jeff@dev817sol.us > /
Package body created.
Then the user executes the package:
jeff@dev817sol.us > set serveroutput on
jeff@dev817sol.us > exec dbms_output.put_line(foo.bar);
this is a package variable.
PL/SQL procedure successfully completed.
Now another sqlplus session is started in window#2 and the user executes the package:
session2 > set serveroutput on
session2 > exec dbms_output.put_line(foo.bar);
this is a package variable.
PL/SQL procedure successfully completed.
Now, the user needs to recompile the body of foo. If he recompiles the package an ORA-4068 error will be generated in session2. Before the first session recompiles the package body, I want to be able to find out if he will generate an ORA-4068 error for any other users using that package.
Anybody have any magic queries that can tell me if compiling the package body of foo will produce an ORA-4068?
-
as one developer told: "we were never short of reasons to get this error".
The only thing to do is to design application and maintenance procedures such that the damage is minimized as much as possible. Note that Oracle 8 tries to minimize the occurence of such kind of errors as well as second call to the package can finally succeed.
-
Does both the users have the same permissions granted?
Sam
-
Acutally, it's the same user, just with two different sessions.
-
If your db can not be restricted while doing maintenenace,
then consider having all pl/swl in two different schemas S1 and S2, and tables in schema D.
Then you can run users on schema S1 (for example, with alter session set current schema), while upgrading S2.
Then just edit config for new connections to go over S2, while S1 can be used for maintenance.
I think this method is what is usually used in all OS level app: e.g. different Oracle versions reside in different homes.
In fact, Oracle allows some vulnerability by allowing upgrade of packages that are loaded (not currently executed, but loaded) by other sessions.
