Looking for a workaround to %RowType not including oracle table Invisible columns. I want invisible columns so as not to effect legacy code during a transition, and be able to use %RowType or similar in the new code to access all the columns. One thing I tried is creating a Type Record with the full table structure, but it does not seem to allow %Type references to individual columns, i.e.
Type t_Inv_Test Is Record
(
Test_Column_Vis Varchar2(20),
Test_Column_Inv Varchar2(20)
);
Cannot do:
Function Qry(p_Test_Val In t_Inv_Test.Test_Column_Vis%Type)
Return t_Inv_Test.Test_Column_Inv%Type;
After looking at other Invisible column questions I am also considering defining a view with all columns and then use the View%Rowtype. What is the best way to do this? Thanks Joe
This sounds like a use case for Oracle Edition-based Redefinition. EBR allows us to maintain two different versions of our data model in one live database. It is the sort of highly neat functionality which Oracle provides and that justifies the license cost (discuss).
Anyway you should definitely check it out before you embark on hand-rolling your own implementation of it. Find out more
Yes, there is only one version of the actual table. What EBR enables is the presentation of different projections of the table to different users. The idea is you define an edition before you add the column to the table. Users connecting using the old edition see the version of the table without the column; switch to the new edition and they see the column. Once you have migrated all your legacy apps to the new model you can retire the old edition.
This magic is achieved through views and triggers, pretty much as you propose doing, but with the guarantee of robustness which comes from using Oracle built-in functionality.