I have a user defined database package which is used quite heavily. When I need to update the code body, I will get several
ORA-04061: existing state of package body "CS.PACKAGE" has been invalidated
ORA-04065: not executed, altered or dropped package body "CS.PACKAGE"
ORA-06508: PL/SQL: could not find program unit being called: "CS.PACKAGE"
ORA-06512: at "CS.PROCEDURE", line 228
We are using a connection pool. How do I put the changes into PACKAGE, without getting several of the above errors?
I cannot control the use of the package, and it is very heavily used.
There are a few approaches you can take:
Remove all package stateRecode the package so it has no global variables or other package-level state. If you have lots of these and removing them is lots of effort, a workaround could be to move them into their own package as Connor demonstrates here:
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:9539610000346461093 Define it as serially_reusableDoing this means packages only retain state for the duration of the call. After this it's reset. This means you won't get the error as there's no state to preserve.
This change can lead to unexpected/unwanted outcomes, so be sure that it meets your requirements before blindly implementing it. The docs have some worked examples to show the impact:
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-packages.html#GUID-0881061B-1F72-4F13-8BBA-B0959199851B__BABFAFDC Use Edition-based RedefinitionEdition-based redefinition is a technology to help you do zero-downtime database deployments.
The basic concept is you create editions. These extend the namespace so you can have many versions of a PL/SQL object in the database.
When changing a package (or any PL/SQL unit), you:
- Create a new edition
- Connect to it & deploy your changes
- Migrate the connections from the current to new edition
Personally, this is my favourite option, as besides avoiding the ORA-4061 error, it has several benefits:
- You can deploy changes anytime without an outage
- You can do basic testing of the release before making it accessible to customers
- The ability to do rolling releases
- Easy rollback (just connect apps back to the previous edition)
https://speakerdeck.com/chrissaxon/sql-changes-with-edition-based-redefinition