Welcome to the Beyond Blog

John's Blog

The views expressed in this blog and completely my own and do not reflect those of Beyond Systems Ltd. All content is provided for informational purposes only, and you are solely responsible for how you use this on your own or others systems. We accept no liability for any losses or damages caused.
2 minutes reading time (382 words)

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.

Combining Facts/Subject Areas in Oracle OBIEE
Advanced Analytics with Oracle BI 12c
 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Sunday, 12 July 2020

Demonstration

Request a demo of our products here

REQUEST DEMO

Contact

Beyond Systems Limited

Suite 1.01 Jactin House,

24 Hood Street, Manchester

M4 6WX United Kingdom

 

 

Tel:    +44(0)8450 940 998

Email:  contact@wegobeyond.co.uk