Skip to Main Content
  • Questions
  • How to update a user defined database package in production


Question and Answer

Chris Saxon

Thanks for the question, Dave.

Asked: April 09, 2024 - 6:08 pm UTC

Last updated: April 19, 2024 - 4:47 pm UTC

Version: Oracle Database 19c Enterprise Edition Release - Production

Viewed 1000+ times

You Asked

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.

and Chris said...

There are a few approaches you can take:

Remove all package state

Recode 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:

Define it as serially_reusable

Doing 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:

Use Edition-based Redefinition

Edition-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)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library