Table configuration in E-Business Suite R12.2 to support Online Patching

With a lot of E-Business Suite customers looking to upgrade to R12.2.x within the near future (if they haven’t already), I thought it might be useful to illustrate some of the key differences. I’ll spread this out over a number of different posts, however for today I am going to look at database tables. Prior to Release 12.2, the following structure was used for E-Business Suite tables.

Old Table Structure

So we have tables created within their application owning schema which are then granted up to the APPS user. Typically the process for doing this (for a custom development) would be:

-- As your custom application user, i.e. XXJK. Create Table xxjk_demo (   id       Number,   val      Varchar2(100),   a_field  Varchar2(10) );  Grant All On xxjk_demo To Apps With Grant Option;  -- As Apps Create Synonym xxjk_demo For xxjk.xxjk_demo; -- Apps grants out any further privileges required. 

However… this changes with R12.2 due to Online Patching. We no longer have the simple model above, but something (only slightly) more complicated.


 

New Table Structure

So now we have a situation where we have a table with a view on top of that table (which is the table name suffixed with a hash) and then the synonym points to that view. This isn’t done manually however; the developer creates the table, grants and then calls the procedure ad_zd_table.upgrade() which does the rest (creates the view and synonym). So you’re never actually querying/updating the table itself, but the key-preserved view.
This is to support Online Patching and uses Edition Based Redefinition which allows the view to maintain a consistent view of the table whilst changes are made.

Our process now is

-- As your custom application user, i.e. XXJK. Create Table xxjk_demo (   id       Number,   val      Varchar2(100),   a_field  Varchar2(10) );  Grant All On xxjk_demo To Apps With Grant Option;  -- As Apps exec ad_zd_table.upgrade(x_table_owner=>'XXJK',x_table_name=>'XXDEMO') 


So if we consider a simple example, in DBA objects we have:

Select owner, object_name, object_type    From dba_objects   Where object_name Like 'XXDEMO%';    Owner Object_Name Object_Type APPS  XXDEMO      SYNONYM XXJK  XXDEMO      TABLE XXJK  XXDEMO1     INDEX XXJK  XXDEMO#     VIEW 

The whole topic around this is actually quite huge and gets pretty complicated when considering data integrity with EBR, however that is quite nicely documented on My Oracle Support. I will cover some bits of that another time with some examples.

Leave a Reply

Your email address will not be published. Required fields are marked *

8 + fifteen =

This site uses Akismet to reduce spam. Learn how your comment data is processed.