Introduction:
Edition-Based Redefinition is an essential feature in Oracle, designed for oracle database administrators, developers who manage application upgrades. EBR allows for seamless updates with minimal or no downtime, enabling the management of multiple versions of database objects simultaneously. In this blog, we’ll delve into the key differences between editioned and non-editioned objects, offering insights to help you leverage EBR effectively in your Oracle environment.
Edition-Based Redefinition (EBR): Edition-Based Redefinition is a powerful feature that allows you to manage application upgrades with minimal downtime.
Here are the main types of editioned and non-editioned objects
I) Editioned Objects
These objects can have multiple versions across different editions:
- PL/SQL Objects: Functions, procedures, packages (both specification and body)
- Views: Including editioning views
- Synonyms
- Triggers
- Types: User-defined types within PL/SQL packages
II) Non-Editioned Objects
These objects are shared across all editions and cannot have multiple versions:
- Tables
- Indexes
- Materialized Views
- Sequences
Editioning is particularly useful for applications that require continuous availability, as it allows you to apply changes in a new edition while the current edition remains active.
In our case, while recompiling procedure, we are Getting Error “PL/SQL: ORA-38818: illegal reference to editioned object” in EBS Database.
The error ORA-38818: illegal reference to editioned object occurs when a non-editioned object tries to reference an editioned object. This violates the rule that a non-editioned object cannot depend on an editioned object.
To resolve this issue, you have a couple of options:
a) Make the referencing object editioned:
This aligns both objects under the same editioning rules.
b) Avoid referencing editioned objects:
Ensure that non-editioned objects do not reference editioned objects.
Again if you go with edition enable method, we can follow any one of following two.
- By enabling editioning at schema level . Schema Name “XXCUST_TBI”.
- By enabling editioning at object type level(procedure or Package or Trigger etc..)
Issue Description:
SQL> @XXDY_REV_COST_PROC.sql
54 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE XXDY_REV_COST_PROC:
LINE/COL ERROR
——– —————————————————————–
11/1 PL/SQL: SQL Statement ignored
22/6 PL/SQL: ORA-38818: illegal reference to editioned object .
ORA-38818: illegal reference to editioned object
APPS.RCV_SHIPMENT_LINES
31/1 PL/SQL: SQL Statement ignored
43/6 PL/SQL: ORA-38818: illegal reference to editioned object .
ORA-38818: illegal reference to editioned object
APPS.RCV_SHIPMENT_LINES
SQL>
XXCUST_TBI schema we have recently migrated from Stand alone environment to EBS Database.
While recompiling one of procedure, we are getting above error. XXCUST_TBI is non-editioned Schema. APPS. RCV_SHIPMENT_LINES is editioned synonym.
If We enable editioning at specific TYPE level, XXCUST_TBI can have non-editioned and editioned objects. If we enable editioning at Schema Level, All objects owned by XXCUST_TBI will become editioned. Tables can’t be editioned always.
SQL> alter user XXCUST_TBI enable editions for PROCEDURE;
User altered.
After enabling editioning for object TYPE procedure, we could able to compile procedure without any errors.
Conclusion:
Once Editioning Enabled at Schema Level or Object Type Level, that can’t be reverted. If we enable editioning at Object Type Level, we can maintain Editioned and Non-Editioned Objects within Schema.
