Detecting ORA 4068 Errors
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Detecting ORA 4068 Errors

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Question

    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?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Nov 2000
    Posts
    212
    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.


  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Does both the users have the same permissions granted?

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Acutally, it's the same user, just with two different sessions.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Posts
    212
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width