I've noticed that whenever I have package-level constants (or any variables for that matter), whenever I recompile the package, persistent connections to the database get an error that "existing state of package body has been invalidated".
Is there a way to avoid this? Perhaps through synonyms? What are best-practices in this case?
Hi Superdario,
In general, you should avoid replacing code in a live production instance.
If you really really have to be live 24/24 7/7, and you can't schedule ANY (even tiny) downtime you will have to avoid package-level variables since recompilation of a such a package will trigger the aforementionned error.
You could also catch the error in your client application and decide what to do. Maybe you have sufficient information to restart whatever the client was doing.
This thread on AskTom covers the same topic.
The problem is if, at 10:00am, a session starts and the constant is set to 'A', then at 11:00am you change it to 'B', then the session 'blows up' in confusion.
The Serially_Reusable pragma may work for you. Basically it won't preserve state between calls. So at 11:0am it will just start using 'B'. If you can be 100% sure that won't break your code, it can work. Re-initializing the constants whenever they are needed may be an overhead.
Also look at calling DBMS_SESSION.MODIFY_PACKAGE_STATE or DBMS_SESSION.RESET_PACKAGE at appropriate intervals. That may reduce the number of errors you get.
You should also look at Edition-based redefinition in the new 11gR2. That's a more comprehensive solution, but I guess you'd need to upgrade for that.
In the past I've gotten around this by moving all state-related stuff into separate packages.
For example, if I had a package "CUSTOMER_PKG", I'd move all the global variables into a spec-only package called "CUSTOMER_GLOBALS_PKG".
Unfortunately this means exposing all private globals that were defined in the package body. We had to enforce a development standard so that CUSTOMER_GLOBALS_PKG was only allowed to be referred to by CUSTOMER_PKG.