The editioning view (EV) is a new object type that sits between the table (which cannot be editioned) and the code objects that reference the table. It acts like a table in many ways – you can do DML without an instead of trigger, and you can create a normal trigger on the EV.
The main function of an EV is to allow you to alias columns, and to make those aliases editionable. It is functionally close to an editioned table. You can’t restrict rows or join tables in an EV. The set of allowed actions is really quite restrictive, but the ability to change the underlying column that a view column points to is a powerful feature that you will end up using quite a lot, so plan to switch all your tables to use EV’s in your conversion plans.
Converting a table to use an EV involves the following steps:
1. Save the source of all the triggers on the table
2. Generate and save the DDL for the grants to this table
3. Drop any public synonyms that point to the table
4. Rename the table – I added a ‘_’ to the end of each table. Any that failed because the resulting name was too long has the last char of the name replaced with a ‘_’. This is needed because the EV occupies the same Oracle namespace as the table, and you don’t want to have to change the name in all of your regular views and PL/SQL.
5. Create an EV for the table with the same name as the old table – “create editioning view mytable as select * from mytable_;”
6. Drop the triggers from the table and create them on the EV, using the source code you saved in step #1.
7. Run the grant DDL from step #2 to allow all the access to the EV that the original table had. Revoke the grants from the table, everyone must now use the EV.
8. Create private synonyms for any schemas that were using the public synonym. If you lack the time to determine this, you can create them for all the users. I addressed the synonym issues in an earlier post.
9. Recompile all the objects that are invalid.
When you are done the indexes and constraints still refer to the underlying table, but all of these will reference the EV:
triggers
views
PL/SQL program objects (packages, standalone procs and funcs)
synonyms
grants