Oracle Live SQL - Script: Remove columns from query output

Statement 1

With standard SQL, to exclude one column from the results you need to list all the other columns except this one

Omit HIRE_DATE

select employee_id, first_name, last_name, email, 
       phone_number, job_id, salary, commission_pct, 
       manager_id, department_id 
from   hr.employees


EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
100 Steven King SKING 515.123.4567 AD_PRES 24000  -  - 90
101 Neena Kochhar NKOCHHAR 515.123.4568 AD_VP 17000  - 100 90
102 Lex De Haan LDEHAAN 515.123.4569 AD_VP 17000  - 100 90
103 Alexander Hunold AHUNOLD 590.423.4567 IT_PROG 9000  - 102 60
104 Bruce Ernst BERNST 590.423.4568 IT_PROG 6000  - 103 60

Rows 1 - 5. More rows exist.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
100	Steven	King	SKING	515.123.4567	AD_PRES	24000	-	-	90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	AD_VP	17000	-	100	90
102	Lex	De Haan	LDEHAAN	515.123.4569	AD_VP	17000	-	100	90
103	Alexander	Hunold	AHUNOLD	590.423.4567	IT_PROG	9000	-	102	60
104	Bruce	Ernst	BERNST	590.423.4568	IT_PROG	6000	-	103	60

Statement 2

SELECT ... EXCEPT is invalid syntax in Oracle Database

Try SELECT EXCEPT

select * except hire_date 
from   hr.employees

ORA-00923: FROM keyword not found where expected

More Details: https://docs.oracle.com/error-help/db/ora-00923

Statement 3

When writing multi-stage queries, you may want to remove working columns from the results. For example, omit RN from this top-N/group query

Top-N per group

with rws as ( 
  select e.employee_id, e.first_name, e.last_name, 
         row_number() over (  
           partition by department_id 
           order by hire_date 
         ) rn 
  from   hr.employees e 
) 
  select * from rws 
  where  rn <= 3


EMPLOYEE_ID FIRST_NAME LAST_NAME RN
200 Jennifer Whalen 1
201 Michael Hartstein 1
202 Pat Fay 2
114 Den Raphaely 1
115 Alexander Khoo 2

Rows 1 - 5. More rows exist.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	RN
200	Jennifer	Whalen	1
201	Michael	Hartstein	1
202	Pat	Fay	2
114	Den	Raphaely	1
115	Alexander	Khoo	2

Statement 4

The describe function accepts a list of columns you want to remove from the results.

Remove columns PTF spec

create or replace package except_cols_pkg as  
  
  function describe (  
    tab         in out dbms_tf.table_t,  
    except_cols dbms_tf.columns_t  
  ) return dbms_tf.describe_t;  
 
end except_cols_pkg;

Package created.

Statement 5

This excludes all listed columns by setting their for_read and pass_through properties to false.

Remove columns PTF body

create or replace package body except_cols_pkg as  
  
  function describe (  
    tab         in out dbms_tf.table_t,  
    except_cols dbms_tf.columns_t  
  ) return dbms_tf.describe_t as  
  begin  
      
    for i in 1 .. tab.column.count loop  
      
      if tab.column(i).description.name   
         member of except_cols then  
           
        tab.column(i).for_read := false;  
        tab.column(i).pass_through := false;  
          
      end if;  
        
    end loop;  
     
    return dbms_tf.describe_t ();  
  
  end describe;  
    
end except_cols_pkg;

Package Body created.

Statement 6

Remove columns PTF function

create or replace function except_cols (  
  tab         table,     
  except_cols columns  
) return table pipelined   
  row polymorphic   
  using except_cols_pkg;

Function created.

Statement 7

This removes all the listed columns from the query results

Remove columns PTF examples

select *  
from   except_cols (  
  hr.employees,  
  columns ( employee_id, phone, salary, commission_pct, manager_id, job_id )  
)


FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE DEPARTMENT_ID
Steven King SKING 515.123.4567 17-JUN-03 90
Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 90
Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 90
Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 60
Bruce Ernst BERNST 590.423.4568 21-MAY-07 60

Rows 1 - 5. More rows exist.

FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	DEPARTMENT_ID
Steven	King	SKING	515.123.4567	17-JUN-03	90
Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	90
Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	90
Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	60
Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	60

Statement 8

Remove columns PTF examples

select *  
from   except_cols (  
  hr.departments,  
  columns ( department_id )  
)


DEPARTMENT_NAME MANAGER_ID LOCATION_ID
Administration 200 1700
Marketing 201 1800
Purchasing 114 1700
Human Resources 203 2400
Shipping 121 1500

Rows 1 - 5. More rows exist.

DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
Administration	200	1700
Marketing	201	1800
Purchasing	114	1700
Human Resources	203	2400
Shipping	121	1500

Statement 9

The PTF removes the listed columns from that point in the query. So you can't use them in any other clauses - e.g. WHERE

Filtering excluded columns

with rws as ( 
  select e.employee_id, e.first_name, e.last_name, 
         row_number() over (  
           partition by department_id 
           order by hire_date 
         ) rn 
  from   hr.employees e 
) 
  select * 
  from   except_cols ( rws, columns ( rn ) ) 
  where  rn <= 3

ORA-00904: "RN": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 10

If you want process an excluded columns (e.g. in the WHERE clause), you must do so in a subquery. Then exclude it in an outer query.

Filtering excluded columns

with rws as ( 
  select e.employee_id, e.first_name, e.last_name, 
         e.department_id, 
         row_number() over (  
           partition by department_id 
           order by hire_date 
         ) rn 
  from   hr.employees e 
), top_n_rows as ( 
  select * from rws 
  where  rn <= 3 
) 
  select *  
  from   except_cols ( top_n_rows, columns ( rn ) )


EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID
200 Jennifer Whalen 10
201 Michael Hartstein 20
202 Pat Fay 20
114 Den Raphaely 30
115 Alexander Khoo 30

Rows 1 - 5. More rows exist.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	DEPARTMENT_ID
200	Jennifer	Whalen	10
201	Michael	Hartstein	20
202	Pat	Fay	20
114	Den	Raphaely	30
115	Alexander	Khoo	30

Statement 11

This enables you to omit columns by name or type

Remove columns or types overloaded PTF spec

create or replace package except_cols_pkg as  
  
  function describe (  
    tab         in out dbms_tf.table_t,  
    except_cols dbms_tf.columns_t  
  ) return dbms_tf.describe_t;  
   
  function describe (  
    tab         in out dbms_tf.table_t,  
    except_type varchar2 
  ) return dbms_tf.describe_t;  
   
  function except_cols ( 
    tab         table,     
    except_cols columns 
  ) return table pipelined   
    row polymorphic   
    using except_cols_pkg; 
     
  function except_cols ( 
    tab         table,     
    except_type varchar2 
  ) return table pipelined   
    row polymorphic   
    using except_cols_pkg; 
 
end except_cols_pkg;

Package created.

Statement 12

Remove columns or types overloaded PTF body

create or replace package body except_cols_pkg as  
  
  /* Remove by column names */ 
  function describe (  
    tab         in out dbms_tf.table_t,  
    except_cols dbms_tf.columns_t  
  ) return dbms_tf.describe_t as  
  begin  
      
    for i in 1 .. tab.column.count loop  
      
      if tab.column(i).description.name   
         member of except_cols then  
           
        tab.column(i).for_read := false;  
        tab.column(i).pass_through := false;  
          
      end if;  
        
    end loop;  
     
    return dbms_tf.describe_t ();  
  
  end describe;  
   
  /* Remove by column type */ 
  function describe (  
    tab         in out dbms_tf.table_t,  
    except_type varchar2 
  ) return dbms_tf.describe_t as  
  begin  
      
    for i in 1 .. tab.column.count loop  
     
      if dbms_tf.column_type_name (  
        tab.column(i).description 
      ) = upper ( except_type ) then  
           
        tab.column(i).for_read := false;  
        tab.column(i).pass_through := false;  
          
      end if;  
        
    end loop;  
     
    return dbms_tf.describe_t ();  
  
  end describe;  
  
end except_cols_pkg;

Package Body created.

Statement 13

This excludes all numeric columns from the results

Remove by type example

select *  
from   except_cols_pkg.except_cols ( hr.departments, 'number' )


DEPARTMENT_NAME
Administration
Marketing
Purchasing
Human Resources
Shipping

Rows 1 - 5. More rows exist.

DEPARTMENT_NAME
Administration
Marketing
Purchasing
Human Resources
Shipping

Statement 14

Beware! Shape-defining parameters must be constants. Variables and expressions are null within the PTF spec. This tries to use a variable to remove all the numeric columns. As this NOT a constant, the numeric columns are still in the query results.

Use constants to omit by type

declare 
  exclude_type varchar2(30) := 'number'; 
begin 
  for rws in (  
    select *  
    from   except_cols_pkg.except_cols ( hr.departments, exclude_type ) 
    fetch first 3 rows only 
  ) loop 
    dbms_output.put_line ( rws.department_id ); 
  end loop; 
end;

Statement processed.
10
20
30

Statement 15

You can also use SQL macros to remove columns from SQL queries. Instead of listing which columns to remove, they build up a list of columns to keep in the output.

SELECT EXCEPT macro

create or replace function except_cols_macro ( 
  tab         dbms_tf.table_t, 
  except_cols dbms_tf.columns_t 
) return clob sql_macro is 
  sql_stmt    clob; 
  column_list clob; 
begin 
 
  for col in tab.column.first .. tab.column.last loop 
    if tab.column ( col ).description.name  
         not member of except_cols then 
      column_list := column_list || ',' || tab.column ( col ).description.name; 
    end if; 
  end loop; 
   
  sql_stmt :=  
    'select ' ||  
    trim ( both ',' from column_list ) ||  
    ' from tab'; 
 
  return sql_stmt; 
   
end except_cols_macro;

Function created.

Statement 16

Using SQL maros inside WITH is unsupported

with rws as ( 
  select * 
  from   except_cols_macro ( hr.departments, columns ( department_id ) ) 
) 
  select * from rws

ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported

More Details: https://docs.oracle.com/error-help/db/ora-64630

Statement 17

Using PTFs inside WITH is supported

with rws as ( 
  select * 
  from   except_cols_pkg.except_cols ( hr.departments, columns ( department_id ) ) 
) 
  select * from rws


DEPARTMENT_NAME MANAGER_ID LOCATION_ID
Administration 200 1700
Marketing 201 1800
Purchasing 114 1700
Human Resources 203 2400
Shipping 121 1500

Rows 1 - 5. More rows exist.

DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
Administration	200	1700
Marketing	201	1800
Purchasing	114	1700
Human Resources	203	2400
Shipping	121	1500

Statement 18

Created by Atif Chaudhry, Consulting Member of Technical Staff at Oracle. This macro enables you to choose which columns to include or exclude by partial name and type.

A comprehensive column selection SQL macro

create or replace function Cols(tab         DBMS_TF.Table_t, 
                                names       DBMS_TF.Columns_t default null,  
                            not_names       DBMS_TF.Columns_t default null,  
                                names_like  DBMS_TF.Columns_t default null,  
                            not_names_like  DBMS_TF.Columns_t default null,  
                                starts_with DBMS_TF.Columns_t default null,  
                            not_starts_with DBMS_TF.Columns_t default null,  
                                ends_with   DBMS_TF.Columns_t default null,  
                            not_ends_with   DBMS_TF.Columns_t default null, 
                                types_like  DBMS_TF.Columns_t default null,  
                            not_types_like  DBMS_TF.Columns_t default null,  
                                debug       number            default 0) 
                  return varchar2 SQL_MACRO  
is 
  sqm clob := null; 
  col DBMS_TF.Column_Metadata_t; 
 
  function Match(nam    varchar2, col DBMS_TF.Columns_t,  
                 flags  pls_integer,  
                 flip   boolean default false,  
                 upcase boolean default false) 
           return boolean  
  is 
    lwc constant varchar2(1) := case when bitand(flags,1)!=0 then '%' end; 
    rwc constant varchar2(1) := case when bitand(flags,2)!=0 then '%' end; 
    found boolean := false; 
  begin 
    if col is null then return true; end if; 
    for i in 1 .. col.count loop 
      found := trim('"' from nam)  
               like lwc||trim('"' from case when upcase then upper(col(i)) else col(i) end)||rwc; 
      exit when found; 
    end loop; 
    return (found and not flip) or (flip and not found); 
  end; 
 
  function NameX(col DBMS_TF.Column_Metadata_t) return boolean is 
  begin 
    return Match(col.name,    names,0)  
       and Match(col.name,not_names,0,true);  
  end; 
 
  function NameL(col DBMS_TF.Column_Metadata_t) return boolean is 
  begin 
    return Match(col.name,    names_like,3) 
       and Match(col.name,not_names_like,3,true); 
  end; 
 
  function StarW(col DBMS_TF.Column_Metadata_t) return boolean is 
  begin 
    return Match(col.name,    starts_with,2) 
       and Match(col.name,not_starts_with,2,true); 
  end; 
 
  function EndW(col DBMS_TF.Column_Metadata_t) return boolean is 
  begin 
    return Match(col.name,    ends_with,1) 
       and Match(col.name,not_ends_with,1,true); 
  end; 
 
  function TypeL(col DBMS_TF.Column_Metadata_t) return boolean is 
  begin 
    return Match(DBMS_TF.Column_Type_Name(col),    types_like,3,false,true) 
       and Match(DBMS_TF.Column_Type_Name(col),not_types_like,3,true, true); 
  end; 
 
begin 
  for i in 1 .. tab.column.count loop 
    col := tab.column(i).description; 
    continue when not(NameX(col) and NameL(col) and StarW(col) and EndW(col) and TypeL(col)); 
    sqm := sqm || col.name || ',';             
  end loop; 
 
  if sqm is not null then 
    sqm := 'select '||rtrim(sqm,',')||' from tab'; 
  else  
    sqm := 'select null from dual'; 
  end if; 
 
  if (debug <> 0) then dbms_output.put_line(sqm); end if; 
 
  return sqm; 
end;

Function created.

Remove columns from query output

Additional Information