Backward-compatible SQL query against table with renamed column

user_653539 0
Viewed 83

Let t be an Oracle 11g table with columns a, b. The b column is later renamed to c.

How to develop an SQL query which works against both database versions? "Works" means:

  • returns columns a, c
  • behaves as select a, c from t on new version
  • behaves as select a, b as c from t on old version

The background is, the module with SQL query (it is actually based on Pentaho Data Integration) has deployment lifecycle which does not strictly match the deployment lifecycle of database changes. Also there seems to be no clean way of feature-flagging multiple versions of SQL query. Hence one-SQL-fits-all solution seems to be the simplest one for 100% compatibility.

backwards-compatibility oracle11g common-table-expression oracle sql
2 Answers

You may add a virtual column that projects a new column with an old name. And after you migrate all the consuming code to the new column names, you may drop old virtual columns for free.

create table t (
  a int,
  b int
)

insert into t
select 1, 2 from dual union all
select 3, 4 from dual

create function f
return int
as
  res int;
begin
  select sum(a) + sum(b)
    into res
  from t;
  
  return res;
end;/

alter table t
rename column b to c;

alter table t
add b int generated always as (0+c) virtual

select f()
from dual

| F() |
| --: |
|  10 |

create or replace function f
return int
as
  res int;
begin
  select sum(a) + sum(c)
    into res
  from t;
  
  return res;
end;/

alter table t
drop column b

select f()
from dual

| F() |
| --: |
|  10 |

db<>fiddle here

user_27787100

The simplest solution I could figure out is based on CTE:

with x(a,c) as (select * from t)
select a,c from x

Db fiddle demonstration on two different tables.

The obvious limitation is the number of column must stay same and there must not be any change in column order.

user_6535390
Related