I must possess a column during my DB accessible via two column names temporarily.
Why? The column title was badly selected, I must refactor it. When I want my webapp to stay stable while altering the column title, it might be good to
- possess a (let us refer to it as) symlink named better_column_title pointing towards the column bad_column_title
- alter the webapplication to make use of better_column_title
- drop the symlink and relabel column to better_column_title
"Refactoring Databases" indicates to really give a second column that is synchronized on commit to be able to accomplish this. I'm just wishing that there can be an simpler way with Oracle, with less work and fewer overhead.
As lengthy as you've code that utilizes both column names, I do not see a method to circumvent the truth that you will have two (real) posts for the reason that table.
I'd add the brand new column using the correct title after which produce a trigger that inspections which column continues to be modified and updates the "other" column correspondingly. So whatever has been up-to-date, the worthiness is synch'ed using the other column.
Once all of the code that utilizes that old column continues to be migrated, take away the trigger and drop that old column.
The trigger would so something similar to this:
CREATE OR REPLACE TRIGGER ... ... UPDATE OF bad_column_name, better_column_name ON the_table ... BEGIN IF UPDATING ('BAD_COLUMN_NAME') THEN :new.better_column_name = :new.bad_column_name END IF; IF UPDATING ('BETTER_COLUMN_NAME') THEN :new.bad_column_name = :new.better_column_name END IF; END;
An order from the
IF claims controls which change includes a "greater priority" just in case someone up-to-date both posts simultaneously.
Relabel the table:
alter table mytable rename to mytable_old;
Produce a view using the original tablename with both bad_column_title and_column_title that could indicat exactly the same column (not to mention the rest of the posts):
create or replace view mytable as select column1 , column2 , ... , bad_column_name , bad_column_name better_column_name from mytable_old ;
Because this view is updatable automatically (I suppose here that mytable includes a primary key), you are able to place/update/remove in the view also it does not matter if you are using bad_column_title or better_column_title.
Following the refactoring, drop the vista and relabel the table and column:
drop view mytable; alter table mytable_old rename column bad_column_name to better_column_name; alter table mytable_old rename to mytable;
The very best means to fix this really is only accessible in Oracle 11g Release 2: Edition-based Redefinition. This really awesome feature enables us to keep different versions of database tables and PL/SQL code, using special triggers and sights. Find out more.
Basically this really is Oracle's built-in implementation of @AHorseWithNoName's suggestion.
you may create a view for that table. And port the application to make use of that view rather than the table.
create table t (bad_name varchar2(10), c2 varchar2(10)); create view vt as select bad_name AS good_name, c2 from t; insert into vt (good_name, c2) values ('blub', 'blob'); select * from t; select * from vt;
If you are on 11g you could think about utilizing a virtual column. I'd most likely be enticed to alter an order slightly relabel the actual column and make the virtual one while using old (bad) title, which could then be dropped at leisure. You might be restricted, obviously, there might be implications on other objects being invalidated which make this order less appropriate for you personally.