Contact us at +1.415.901.7500 or contact@itconvergence.com

“Collaborate 13 Outtakes” on Oracle R12.2 and Customizations

“Collaborate 13 Outtakes” on Oracle R12.2 and Customizations
At Collaborate 13 IT Convergence was fortunate enough to be invited by Oracle Corporation to copresent the session “10 Things You Can Do Today to Prepare for Oracle R12.2.” Kevin Hudson & Nadia Bendjedou represented Oracle while Gustavo Gonzalez represented IT Convergence.

 

As part of our “Collaborate 13 Outtakes” series we’re happy to bring you an extended post on customization issues and Oracle R12.2 prepared by the leader of the Buenos Aires offers of our Near Shore Development Services team .

 


Online Patching and Customized Code in Oracle E-Business Suite R12.2

The new Online Patching feature introduced with R12.2 calls for a new set of standards and best practices when writing new customizations or upgrading the ones created for pre-R12.2 releases.
This note provides a summary of the Online Patching feature and a summarized description of its impact in customized code as well as tips and recommendations to upgrade your customizations.

 

Online Patching is one of the most important and impressive new features of R12.2.
With pre-R12 releases, patching requires some time and there are different techniques to reduce it, but still some time is required to apply patches. Online patching allows the application of patches “while” the application is still running and only a very short, predictable period of downtime is required.

 

The way it works involves 3 steps that can be summarized as follows:
  • Step 1: Code in the database and in the filesystem is copied into a different “Edition”. Editions are a new feature in the database that allow different versions of a same object to be stored on different Editions. It’s important to note that Editions are not database schemas, but a new way to store objects.
    As Editions do not exist on the operating system, filesystem objects are “copied” to a different directory structure.
  • Step 2: Patches are applied to the Edition used for patching, called the “Patch Edition”. This occurs while users are still working with the production or “Run Edition”.
  • Step 3: When the patches are ready, users disconnect from the Run Edition, Editions are “swapped” and users re-connect to the patched Edition.
The new architecture required to support this process is based on the new “Editioned Based Redefinition” (EBR) database feature,

 

– EBR allows the editioning of some object types, while others may not be editioned. Editioned objects can have different versions on each Edition, while non-editioned objects are exactly the same across Editions.
Editionable objects are those that do not contain data, like Views, PL/SQL code , synonyms, user tyes and virtual private database policies. Non-Editionable objects are those that contain data, like tables, temporary tables, materialized views, indexes, sequences and database links.

 

Editioning Views are a new object type supported by the database that provide an edition-like representation of an object, retrieving the correct portions of the object that are named after the Edition names.

 

This means that an editionable object (for example, a table) has different versions on different Editions, the Editioning View will always retrieve the correct representation of the table based on the Edition you’re currently working with.

 

So all table access has to be done through the Editioning Views and the embedded logic will retrieve the correct representation of the table based on the information about the Edition you’re working with that’s stored on your database context.

Database EBR incorporates new features to:

  • Declare a schema as “editionable”
  • Declare an object as “editionable”
  • Utilities to switch from one edition to another
  • Report to validate schema compliance with the model
  • Utilities to access one edition from another
  • Dictionary views to query the status of editions
  • Best practices to manage some common problems

So let’s recap about the terminology introduced:

Edition
●  Nonschema objects with no owners
●  Multiple Editions can coexist in a db
●  The db must have at least one Edition.
Editioned Object
●  A schema object with an editionable type and an editions-enable owner
●  An Edition can have its own copy of an editioned object, visible to the edition only
●  Uniquely identified by Object_Name, Owner and Edition_Name
Noneditioned Object
●  A schema object with a noneditionalbe type
●  Is identical and visible to all the editions
●  An Edition cannot have its own copy of an editioned object
●  Uniquely identified by Object_Name and Owner
Potentially Editioned
●  Editionalbe type and not editions-enabled owner

 

Edition names are not required to reference an object. It’s retrieved from the Contex.

Problems with Online Patching

 

For all of its benefits, the online patching architecture presents a number of limitations that have to be considered during the development of customizations in orrder not to loose them during patch application (remember all patches starting with but but not including Oracle R12.2 will be online-patches).

 

For example, code dynamically generated through dynamic PL/SQL is not supported. So programs that generate dynamic code must be disabled during an online patch application.

 

Another issues is that noneditioned objects cannot depend on an editioned object. This means that a public synonym cannot refer to an editioned object; a function-based index cannot depend on an editioned function; a materialized view cannot depend on an editioned view; etc.

 

Crossedition Triggers are another database feature that allows programs running on one Edition to access the representation of an object for another Edition.

 

This feature is used to update data that may be required on another Edition, but not on the current one.

 

Development Standards for Oracle R12.2

 

Code developed for R12.2 and code that will eventually be upgraded to R12.2 must follow some guidelines in order for the Online Patch must not break the Running Application, and for the Running Application not to break the Online Patch.
  • A Customization must only change editionable objects in the Patch Edition
  • A Customization must not make incompatible changes to non-editioned objects or data used by the Running Application (ex. Modify column types)
  • The Running Application must replicate editioned object changes to the Patch Edition (ex. Dynamic code)
  • The Running Application must not make incompatible changes to non-editioned objects maintained by Online Patching (ex. Add columns to a table)
  • If an object is generated dynamically during application execution while a Patch Edition exists, the same object must be created on the Patch edition
  • PL/SQL Packages, Procedures and Functions:
    • Object Name must end with an alphanumeric character
    • If the Running Application creates, replaces or drops PL/SQL code while a Patch Edition exists, then the same action must be executed in the Patch Edition
  • PL/SQL Triggers
    • Trigger Name must end with an alphanumeric character
    • Trigger must be on the Editioning View, not the table
    • Tip: Create the trigger on the APPS table synonym, and the resulting trigger will be created on the EV
    • EV Triggers reference logical columns via the editioning view, rather than actual table columns
    • If the Running Application creates, replaces or drops triggers while a Patch Edition exists, then the same action must be executed in the Patch Edition
  • Synonyms
    • Table Synonym must point to the Editioning View
    • Use APPS synonyms to reference EBS tables. Do not reference tables directly
    • If the Running Application creates, replaces or drops synonyms while a Patch Edition exists, then the same action must be executed in the Patch Edition
  • Tables
    • Table Names shall not use the “#” character
    • Table Names must be unique within the first 29 characters
    • Tables must be owned by an EBS product schema, not by APPS
    • Tables will automatically have an EV that maps logical column names to the correct storage column, per edition
    • Tables will automatically have an APPS synonym that points to the EV
    • Column Names must only use “#” as the last character
    • Column Names must be unique within the first 27 characters
    • LONG and ROWID column types are not supported
    • Queries must access the table via the APPS synonym or the EV
  • Indexes
    • Index Names must contain an underscore “_”
    • Index key lenght less than 3,125 bytes
    • A Function-based index must not reference editioned EBS objects
  • Materialized Views
    • Name must be unique within the first 29 characters
    • May not reference PL/SQL functions (which are editionable)

 

Additional Oracle R12.2 Resources
If you’re want to know more about Oracle R12.2 and what it can do for you, then you have plenty of places to turn:
  • Visit our Collaborate 13 Resources Page and download ITC’s portion of the “10 Things You Can Do Today to Prepare for Oracle R12.2” presentation
  • Check out ITC’s experience as an Oracle R12.2 Beta Tester

As always, if you’d like to talk to one of our experts who has familiarized himself with Oracle R12.2 simply click here to fill out our contact us form and we’ll be happy to set up an appointment.

Follow Collaborate 13 as it happens. Click here to access the full stream of this blog’s Collaborate 13 conversation.