Skip to Main Content
  • Questions

  • Execute Immediate and Database Editioning

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jeff.

Asked: February 10, 2021 - 5:42 pm UTC

Last updated: February 11, 2021 - 6:09 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

We have a legacy database schema that has a custom application written that is being supported to end of life, hopefully in the near future.
We do not wish to go through the process of prepping it so its schema is EDITIONED.
(ALTER USER **schema_name** ENABLE EDITIONS;)

We do have some calls to PL/SQL objects (functions,procedures) to our ORACLE EBS instance schema (APPS) on the same database which has been upgraded and is now editioned.

Those legacy pl/sql object calls receive compilation errors (PLS-00754) that they cannot reference editioned objects.

We found we can modify the legacy application to use EXECUTE IMMEDIATE statements and we avoid the compilation restriction for editioning.

My question is this a sound approach for the short term / long term?
Do we need to edition the legacy database schema?
Is there an industry best practice for this scenario if we do not wish (effort and availability of contractors to work on legacy app) or are unable to EDITION the custom schema?

Much appreciated.

and Chris said...

I don't understand why you've editioned the schema - please could you clarify your reasoning?

For background:

Edition-based redefinition is a feature to help you make database schema & PL/SQL changes with zero application downtime. I don't see how your use case relates to this.

With regards to using EXECUTE IMMEDIATE:

This is something you want to avoid. Fortunately I don't think this is necessary.

You're hitting the PLS-00754 error because you have non-editionable objects (things you can only have one copy of) that reference editionable objects (things you can have many copies of). There are a couple of ways around this:

Editions enable all the schemas involved.

OR

Declare the specific objects that you access as non-editionable:

create or replace noneditionable type ...

If you give more background as to why you're editions enabling your schemas and what you hope to gain by doing this we may be able to give more help.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Clarification and thanks

Jeff, February 11, 2021 - 3:31 pm UTC

I was not aware of the option to force an object to be non-editioned. This has solved my issue.

For clarification that might help others. My source instance APPS that we are calling objects from is editioned only because the oracle EBS product requires it for the patches and support we receive. The Destination legacy app is not editioned since it is a legacy custom application. We would not use editioning in apps if we had a choice. We don’t require the high availability that editioning provides.
Chris Saxon
February 11, 2021 - 6:09 pm UTC

Got it, that makes sense :) Glad this helped
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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