Thanks Chris for the answer and nice workarounds.
Still it will not serve the application upgrade purpose, until we have editionable table.
The requirement is application upgrade also has some data changes in the application repository tables, its not structural table changes, but table data changes (both insert of new rows and updates/deletes of existing rows). How can this be restricted to new edition till the edition is actualized.

I liked the first approach, why don't we have an enhancement keep a additional column somewhere for keeping edition#, show only those rows in the edition1, and follow your first approach of creating views with where for old and for next new one put edition2 for edition#...so on.. :)

Wow as Tom mentioned, its a killer feature for application upgrade.

By the time i got your reply, I gave the below workaround/solution, let me wait for the customer feedback.
I m pasting here, if anyone has this kind of questions.

Some how we need to encapsulate the new data changes inside a new editionable procedure created only in child edition 2 and run that procedure after new edition actualized. This will eliminate people in base edition 1 to see and run it accidentally.
if the new data changes are huge, create a new table similar to old say T2_ and create editionable view T2 for this new table T2_ and then in child edition 2 a new editionable procedure to populate the old or T1 editionable view from T2 editionable view.
Once the data changes happens drop the T2, T2_ and the new editionable procedure to do data changes.

I gave this based on your feedback

Add a column to existing table T1_ say ed_number and update the whole table with 1 and somehow make it to contain value 1 for all the user inserts (i.e., new rows) in edition1
create new normal view T1 with where condition to include only ed_number=1 (which is editionable ! ), then make application (like procedures/functions) to point to use this view

in the edition2 change the view to include only ed_number=2
do all your code changes, after actualize, update ed_number to 2 for all the rows, if this update is costly thing to do, then modify view again with where condition ed_number=1 or 2. ;)

1. some how we need to encapsulate the new data changes inside a new editionable procedure created only in child edition 2 and run that procedure after new edition actualized. This will eliminate people in base edition 1 to see and run it accidentally.

2. if the new data changes are huge, create a new empty table similar to old table T1_ say T2_ (create table T2_ select * from T1_ where 1=2) and create editionable view T2 for this new table T2_ and then in child edition2 a new editionable procedure to populate (with applicable business conditions if any) the old table T1_ or T1 editionable view from T2 editionable view.
Once the data changes happens drop the T2, T2_ and the new editionable procedure to do data changes.
Besically T2_ is a place holder table to hold application data changes, which is hidden from edition1 application users, they will have access to T1 view and not T2 view.

Could a Virtual Private Database help with data changes where each table has an additional column that represents the edition this row is effective for?

The added VPD predicate could then add to each statement something like "edition <= CURRENT_EDITION" where CURRENT_EDITION is the effective edition for that user?

Stephen

Thanks Chris & Stephen for valuable inputs.
Let us check, how customer uses mix of oracle technologies to accomplish their Online application upgrade or patching using Edition-Based Redefinition EBR, VPD, etc.

Ya, CURRENT_EDITION_NAME value from USERENV adds complexity, its upto customer. I also suggest to use their application version number or build number, because its basically their application repository data modifications

Hi,

I would like know that how was this problem resolved ultimately. I am facing the same issue using EBR, where I have some DMLs along with DDLs to upgrade the application version.

Thanks,
Shaina

Hi,

We are enabling EBR on 19C . Have encountered following case in our legacy application.

We have a table with column datatype as a type object.
After EBR is enabled, this table is marked as invalid.
This table is refered/used by large no. of functions and procedures. Hence, all these are referencing objects are now invalid.
We cannot change this implementation as this will impact critical business functionality.


Is there any workaround or handling that can be done to manage this case without invalidation of such tables table with column datatype as a type object??

Please suggest.


OBJECT_NAME : TABLE1
TYPE : TABLE
STATUS : INVALID
COLUMN_NAME : ADDITIONAL_DETAILS
DATA_TYPE : TYPE_ADDITIONAL_DETAILS




CREATE TYPE TYPE_ADDITIONAL_DETAILS
AS OBJECT
( id NUMBER(6)
, item_code VARCHAR2(50)
, item_name VARCHAR2(50)
, teritory_code VARCHAR2(50)
, tax_code VARCHAR2(50)
, tax_country_code VARCHAR2(50)
) ;

CREATE NONEDITIONED TYPE TYPE_ADDITIONAL_DETAILS ...

Thanks!!

Issue resolved

We followed below steps before enabling all the schemas as EBR.
a) Restored schema
b) Before enabling EBR,
- Dropped above tables (refering type as column)
- Dropped user defined types
- Re-created types as as NONEDITIONABLE
c) Enabled EBR

Now, table is nit showing as invalid.

Thanks,
Prafull

Thank you for your Responce. I feel this option is good when we have limited Rows in table. is tehre any other options if we want to do same kind of change on transaction tables which has milions of rows?.