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.
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.
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.