Let’s have several Oracle databases and suppose we create table t with columns a, b in some of them.
How to develop an SQL query which can be successfully run against all databases such that
- on databases with tables
tit behaves asselect a, b from t - on databases without table
tit returns empty result set with columnsa,b.
This is another query in line of seemingly-impossible queries (see here and here). The purpose is to overcome some period in database deployment in which the databases differ. Sanitizing differences in client application is comparably more laborious than temporary – yet sort of hacky – solution.
The solution is to utilize XML features. Final result set is always recreated from XML. The XML is either built from real data or faked, depending on information about table existence from database catalog.
<code>create table t as select 'a' as a, 'b' as b from dual
</code>
<code>create table t as select 'a' as a, 'b' as b from dual
</code>
create table t as select 'a' as a, 'b' as b from dual
<code>with singlexml(xmlcol) as (
select xmltype(nvl(
dbms_xmlgen.getxml('select * from ' || (
select case count(*) when 0 then 'dual where 0=1' else max(t.table_name) end
from all_tables t
where t.table_name = 'T' -- change to X for testing nonexistent table
))
, '<?xml version="1.0"?><ROWSET/>'
))
from dual
)
select cols.a
, cols.b
from singlexml
, xmltable('/ROWSET/ROW'
passing singlexml.xmlcol
columns a varchar2(32) path 'A'
, b varchar2(32) path 'B'
) cols
</code>
<code>with singlexml(xmlcol) as (
select xmltype(nvl(
dbms_xmlgen.getxml('select * from ' || (
select case count(*) when 0 then 'dual where 0=1' else max(t.table_name) end
from all_tables t
where t.table_name = 'T' -- change to X for testing nonexistent table
))
, '<?xml version="1.0"?><ROWSET/>'
))
from dual
)
select cols.a
, cols.b
from singlexml
, xmltable('/ROWSET/ROW'
passing singlexml.xmlcol
columns a varchar2(32) path 'A'
, b varchar2(32) path 'B'
) cols
</code>
with singlexml(xmlcol) as (
select xmltype(nvl(
dbms_xmlgen.getxml('select * from ' || (
select case count(*) when 0 then 'dual where 0=1' else max(t.table_name) end
from all_tables t
where t.table_name = 'T' -- change to X for testing nonexistent table
))
, '<?xml version="1.0"?><ROWSET/>'
))
from dual
)
select cols.a
, cols.b
from singlexml
, xmltable('/ROWSET/ROW'
passing singlexml.xmlcol
columns a varchar2(32) path 'A'
, b varchar2(32) path 'B'
) cols
| A | B |
|---|---|
| a | b |
fiddle