T-SQL and PL/SQL query to check if column exists in a table and if it does; use it in the where clause

Question

I have this query in t-sql that checks if column exists in a table, and if it does queries the table with the column in the where clause, and if it does not, then the query is different.

1. Is there a better solution than this?
2. And is it possible to recreate this in Oracle PL/SQL?

--check if dbo.table.UPDATE_STAMP exists
IF COL_LENGTH('dbo.table', 'UPDATE_STAMP') IS NULL
BEGIN
--if dbo.table.UPDATE_STAMP does not exist
    SELECT * FROM dbo.table where CREATE_STAMP > GETDATE()
END
ELSE
BEGIN
--if dbo.table.UPDATE_STAMP does exist
     EXEC('SELECT * FROM dbo.table WHERE UPDATE_STAMP > GETDATE()')
END

Answer 1

Disclaimer

This design is not very natural (at least in Oracle) because you either have the column in the data model and may hardcode it knowing what role it plays in the model definition or you do not have the column and you cannot guarantee that it will mean the same if someone will add it years later. For maintainability it would be much better to have the code static and change it in case of model change instead of dynamic SQL adapting to changes automatically.

Answer

You may use SQL Macro for this (that is available since version 19.7).

If you do not want to create a function for each table, then you may declare the function locally in with function block, but the same query should always return the same table shape, so it may be an issue if you add/drop the column in the same session and reexecute the query again (or you have to specify column list and do not use *).

Setup:

create table test(id, val, dt)
as
select level, lpad(level, 3, '0'), sysdate - level
from dual
connect by level < 6

Query 1:

with function f_dyn_sql
  return clob
  sql_macro(table)
as
  lv_col_exists int := 0;
  lv_stmt varchar2(100) := q'{select id, val from test where 1 = 1}';
begin
  select count(1)
    into lv_col_exists
  from user_tab_cols
  where table_name = 'TEST'
    and column_name = 'DT';

  if lv_col_exists > 0 then
    lv_stmt := lv_stmt || ' and dt > sysdate - 3';
  end if;

  return lv_stmt;
end;

select *
from f_dyn_sql()

ID	VAL
1	001
2	002

Then drop the column and reexecute the query:

update test
set dt = null

5 rows affected

alter table test
drop column dt

with ... <the same code>

select *
from f_dyn_sql()

ID	VAL
1	001
2	002
3	003
4	004
5	005

fiddle