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

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

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

This is similar problem to that one (actually I didn't want to mix them into single question). The CTE-based solution used there is unfortunately not possible here because the number of columns in CTE declaration is fixed while the number of columns in select * clause varies.

The only solution I could figure out is based on conversion between XML and relational model (see self-answer). This is sufficient for my case where table is not large. Anyway I am curious if some pure relational solution (based on joins or Oracle-proprietary constructs such as pivots, model clause etc.) exists.

Please also note this question is not about returning dynamic number of columns from SQL query. The number of columns is fixed, only the underlying data schema changes. Using all_tab_cols is allowed.

CodePudding user response：

The only solution I could figure out so far is based on conversion to XML which is optionally tweaked to some normalized form and then converted back to relations:

with singlexml(xmlcol) as (
  select dbms_xmlgen.getxml('select * from t') from dual
)
, adapted(xmlcol) as ( -- turn <B> into <C> if any
  select xmltype(replace(xmlcol, 'B>', 'C>'))
  from singlexml
)
select cols.a
     , cols.c
     , cols.d
from adapted
   , xmltable('/ROWSET/ROW'
       passing adapted.xmlcol
       columns a varchar2(32) path 'A'
             , c varchar2(32) path 'C'
             , d varchar2(32) path 'D' -- outputs null if none
     ) cols

Db fiddle demonstration on two different tables.

CodePudding user response：

You can check if the colum exists and then run the appropriate `SELECT

DECLARE
  v_column_exists number := 0;  
BEGIN
  Select count(*) into v_column_exists
    from user_tab_cols
    where upper(column_name) = 'b'
      and upper(table_name) = 't1';

  if (v_column_exists = 1) then
      execute immediate 'select a, b as c, null as d from t1';
  ELSE
      execute immediate 'select a, c, d from t2';
  end if;
end;
/