You may create a Polymorphic Table Function available since 18c that will skip columns you specify.
Note:
This approach should be used carefully for very limited cases, because explicit is better than implicit. Either specify star (*) to designate that you want everything or specify columns you want to specify what is expected to be.
Everything except some may be okay as intermediate steps of data transformation where you define those extra columns right in the transformation (for example, union of two CTEs with adjustment of few columns like in unpivot). Otherwise you cannot know the future and cannot guarantee that new columns in the table are those you really want/expect: all except one means everything in the entire Universe except this particular entity.
PTF implementation is in below code:
create package pkg_projection_ptf as
function describe(
p_table in out dbms_tf.table_t,
p_exclude_cols dbms_tf.columns_t
) return dbms_tf.describe_t;
end pkg_projection_ptf;/
create package body pkg_projection_ptf as
function describe(
/*Table identifier*/
p_table in out dbms_tf.table_t,
/*List of column identifiers*/
p_exclude_cols dbms_tf.columns_t
) return dbms_tf.describe_t
as
begin
for i in 1..p_table.column.count loop
for j in 1..p_exclude_cols.count loop
/*pass_through = visible for subsequent processing.
Is TRUE by default, so we need to make it FALSE when required*/
p_table.column(i).pass_through := p_exclude_cols(j) != p_table.column(i).description.name;
exit when p_table.column(i).pass_through = false;
end loop;
end loop;
return null;
end;
end pkg_projection_ptf;/
/*Create PTF that is implemented by the package*/
create function f_projection_exclude(
p_table in out table,
p_exclude_cols columns
)
return table
pipelined row polymorphic
using pkg_projection_ptf;/
Test the function:
create table sample_table (col1, col2, col3) as
select 1, 2, 3 from dual
select *
from f_projection_exclude(
/*Table name and column names are identifiers here, not text literals*/
p_table => sample_table,
p_exclude_cols => columns(col2, col3)
)
| COL1 |
| -----|
| 1 |
select *
from f_projection_exclude(
p_table => sample_table,
p_exclude_cols => columns(col1)
)
| COL2 | COL3 |
| -----|------|
| 2 | 3 |
fiddle
Or the same with SQL Macro available since 19.7 (seems that DBMS_TF parameters are available since 21c, but I have no 19.7 to test this).
with function f(
p_tab dbms_tf.table_t,
p_cols dbms_tf.columns_t
)
return varchar2
sql_macro(table)
as
l_keep_col boolean;
l_cols varchar2(1000);
l_stmt varchar2(1000);
begin
for i in 1..p_tab.column.count loop
l_keep_col := true;
for j in 1..p_cols.count loop
if p_tab.column(i).description.name = p_cols(j) then
l_keep_col := false;
exit;
end if;
end loop;
if l_keep_col then
l_cols := l_cols || ',' || p_tab.column(i).description.name;
end if;
end loop;
l_stmt := 'select ' || trim( both ',' from l_cols) || ' from p_tab';
dbms_output.put_line(l_stmt);
return l_stmt;
end;
select *
from f(sample_table, columns(col2))
| COL1 | COL3 |
| ----:|----:|
| 1 | 3 |
status
dbms_output:
select "COL1","COL3" from p_tab
fiddle
