How to solve ORA-4068

I was amazed by this oneliner in stackoverflow.

First, let me introduce you my old foe, ORA-04068 :
Session 1:
SQL> CREATE OR REPLACE PACKAGE P AS
2 X NUMBER;Y NUMBER;END;
3 /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:
SQL> CREATE OR REPLACE PACKAGE P AS
2 X NUMBER;Z NUMBER;END;
3 /

Package created.

Session 1:
SQL> exec P.X := 2
BEGIN P.X := 2; END;

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

Changing the package in session 2 did invalidate the package variable in session 1.

And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

Session 1:
SQL> CREATE OR REPLACE PACKAGE P AS
2 PRAGMA SERIALLY_REUSABLE;X NUMBER;Y NUMBER;END;
3 /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:
SQL> CREATE OR REPLACE PACKAGE P AS
3 PRAGMA SERIALLY_REUSABLE;X NUMBER;Z NUMBER;END;
2 /

Package created.

Session 1:
SQL> exec P.X := 2

PL/SQL procedure successfully completed.

Oh yes!

10 Comments

  • But that is totally defeating the purpose of global variables in a plsql package!

    The global variable is supposed to retain the value for the life of the session. If you use serially reusable – then the global variable lives only for the duration of the CALL (not the session, just one call to the database).

    If it only needs to live for the life of the call, then it should be a scoped variable in a procedure or function – and then you would not have the 4068 problem at all.

    The ora-4068 happens simply because you have global variables that are RESET to their default when you create or replace the package – and we have to tell you. With serially reusable they are reset every single call! defeating their purpose. If you can use serially reusable, then you didn’t need the global in the first place!!!

    In 11g Release 2 – the real way to “solve” the ora-4068 is to use edition based redefinition. You can put a new version of the package into production without affecting the old package. You would let existing sessions continue to use the old package (and hence their session state, their global variables, would not be affected). New sessions would start using the new package and have their state.

    Serially reusable is not a solution to this in my opinion. IF your code can use serially reusable – THEN it did not need globals in the first place (you are not maintaining a state).

  • SQL> exec P.X := 1

    PL/SQL procedure successfully completed.

    SQL> set serverout on
    SQL> exec dbms_output.put_line(p.x)

    PL/SQL procedure successfully completed.

    Indeed, thanks for the comment, the code is getting pretty useless then!

    I was actually reading 11.2.0.2 Improvements to Reduce the Occurrences of “Existing State of Packages Discarded” Errors
    , and by searching for ORA-4068 I thought I found a useful trick 😉

  • From http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/packages.htm#LNPLS99977 I wonder if there might be a semantics problem – I certainly can’t disagree with Tom’s assertion about global variables and sessions, but I think these are a different animal – variables that only exist for a call – a package can have many logical items within that call. So for those things, you wouldn’t want to add up lots of useless copies in many users UGA’s. Have a pool of them in the SGA, scalability win.

  • Not sure I reckon its as black and white as Tom claims.

    Rightly or wrongly, developers often take their historical 3GL skills (C, C++, etc) and apply those principles to plsql.

    So where as C may have a header file, you often see a plsql package spec defined with “system wide constants”, which are then referenced by many other packages.

    When you change that package of globals (eg add a new one)…then ker splat 🙂

    So I think there *is* potentially a use for the pragma.

    Dont get me wrong – you could workaround the issue in other ways – the globals could be function calls just returning a constant etc…fine with greenfield project – not so fine with an code base that already littered with them.

  • What do you really feel when you tidy up the house? Certainly you are overwhelmed with a sense of pride, fulfillment, and happiness, as if you mosted likely to a MEDICAL SPA hair salon, did a new manicure or wash your vehicle.

    Our company supplies you to existing on your own and your family a real gift – to get house maids Brooklyn as well as get genuine enjoyment without doing anything! Our cleansing firm cleans apartments in New Jacket.

    House Cleaning Service NJ placed points in order, dry and wet tidy, get rid of dust, clean meals, windows and floors, iron clothing and also remake a great deal of various other family jobs. Make use of single services house maids bronx or enter into an irreversible solution contract.
    Ordering our cleaning services such as maids bronx, you save your time and also physical stamina. We understand exactly how to beat the extremely stubborn spots on any surfaces, damage corrosion and provide the furniture its original look.

    We give only competent house cleanings with experience in our cleaning business, which was trained in our business, went to real clients for various sorts of cleaning, and has experience in the household.
    In our work, we use just hypoallergenic and also risk-free detergents, so if you have small children or pet dogs, you can not stress.
    You just require to leave a demand to ensure that we can describe and tell you everything in detail. Call us, contact us and also we will place your residence in order.

    Maid to clean house NY – [url=https://maidsmanhattan.club]maid service new york[/url]

  • Наша компания делает свою работу по восстановлению и очистки труб по водоподготовки и водоотведению новым способом гидродинамической прочистки на новейшем оборудовании. Использование указанного метода дает гарантию возобновления параметров труб к исходному уровню, становятся меньше расходы электроэнергии на 15процентов, повышается продолжительность применения трубопровода на двадцать лет до капремонта, увеличиваются интервалы между профилактическим сопровождением.
    Организуем необходимые виды работ, как например:
    Аварийный ремонт канализации-восстановление внешней системы канализации в течение 2 часов.

    Отчего предпочитают данную фирму?
    Внушительный опыт работы – более 200 восстановленных очистительных сооружений в России и за ее рубежами, присутствие всех разрешения и лицензий на проведение ремонтных и технических задач, разработка и конструирование экологического, безопасного, энергосохраняющего, высокоэффективного оснащения, гарантийное сопровождение, безупречный и серьезный личный состав.

    Бурение скважин на воду : [url=https://akvasos.ru/]Устранение засоров скважин[/url]

  • What do you feel when you tidy up your home? Definitely you are bewildered with a sense of satisfaction, fulfillment, as well as joy, as if you mosted likely to a DAY SPA salon, did a new manicure or clean your car.

    Our company supplies you to existing on your own as well as your household an actual present – to buy house cleanings Staten Island as well as obtain actual pleasure without doing anything! Our cleaning company cleanses apartments in Manhattan.

    House cleanings Classy put things in order, dry and wet tidy, eliminate dust, laundry dishes, windows and floors, iron clothing and also reprise a lot of various other house duties. Take advantage of one-time services the house maids cleaning business or become part of a permanent solution contract.
    Ordering our cleaning services such as house maids, you save your time as well as physical strength. We understand exactly how to beat the very stubborn discolorations on any kind of surfaces, damage rust as well as give the furniture its original appearance.

    We supply only competent house maids with experience in our cleansing company, which was learnt our company, went to real customers for various sorts of cleaning, as well as has experience in the household.
    In our work, we use only hypoallergenic and also safe detergents, so if you have small children or family pets, you can not fret.
    You simply need to leave a demand to make sure that we can explain and also inform you every little thing in detail. Call us, write to us and we will certainly place your home in order.

    Maid service jobs New-York : [url=https://maidsmanhattan.club]maids brooklyn[/url]

Leave a Reply

Your email address will not be published.