How to write SELECT * EXCEPT queries in Oracle Database

Database tables often have many columns. This can make queries omitting some columns tedious to write.

For example, using the HR schema, if you want to exclude hire_date, you have to list out all the other columns:

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

It'd be easier if you can do the opposite: list all the columns you want to hide from the results instead. For example:

select * except hire_date
from   hr.employees;

ORA-00923: FROM keyword not found where expected

But this is invalid syntax in Oracle Database!

In this post we'll see how you can emulate this functionality. We'll cover:

• Polymorphic Table Functions (PTFs)
• SQL macros
• Comparing PTFs and macros
• Summary

Using select * is bad practice. It makes your code more brittle to changes and can make your SQL slower.

This still applies if you're writing select * except. That said, there are a couple of use-cases where this feature is handy:

• When writing ad-hoc queries
• Processing temporary results

Ad-hoc queries

As these are one-offs, it kinda doesn't matter whether you list all the columns or not.

Processing temporary results

Complex queries often have several subqueries. These add working columns necessary to compute the results.

For example, to write top-N per group queries, you need to add row_number to the intermediate result set:

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
        117 Sigal                Tobias                             3
        203 Susan                Mavris                             1
... 

If you want to hide these working columns from the final output, you need to list all the columns out again in the final select.

Provided you've listed all the necessary columns in the subquery, you've avoided the performance and maintenance issues select * brings. In these cases it'd be handy to remove only the working columns from the output.

Write select * except [columns] with Polymorphic Table Functions

Added in Oracle Database 18c, PTFs enable you to define the shape of a result set. So you can add or remove columns from the input table based on the parameters you pass.

The engine of a PTF is a package that has two core components:

• The describe function which sets the columns in the output table
• A fetch_rows procedure to assign values to new columns

In this case we’re only removing columns from the results. So you only need a describe function. Its parameters are the input table and list of columns to remove:

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;  
/

The implementation of this is simple. Check if any of the table's columns are in the exclusion list. If they are, remove them from the output by setting their pass_through property to false:

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;  
/

To use this you need to define the PTF itself. This can be in the package or a standalone function like this:

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

And you're ready to go!

Here are some 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
-------------- ---------------- -------------- -------------------- ----------- -------------
Kimberely      Grant            KGRANT         011.44.1644.429263   24-MAY-2007              
Steven         King             SKING          515.123.4567         17-JUN-2003            90
Neena          Kochhar          NKOCHHAR       515.123.4568         21-SEP-2005            90
Lex            De Haan          LDEHAAN        515.123.4569         13-JAN-2001            90
Alexander      Hunold           AHUNOLD        590.423.4567         03-JAN-2006            60

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
...

Note that this removes the listed columns from the whole statement from that point. This means you can't refer to the excluded columns in the where clause within the current or later subqueries.

For example, you may want to remove the row_number column in a top-N/group query. But if you do this in the same query block you filter it, you'll get an error:

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

To overcome this you need to filter, then exclude.

So to hide rn from the output, place the logic in another subquery. Then remove the unwanted columns in an outer query:

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
        117 Sigal                Tobias                               30
        203 Susan                Mavris                               40 

This works well if you only have a couple of columns to exclude. But what if you want to remove all the columns of a specific type? Say, blob or raw?

Luckily you can adapt the PTF to do this too.

Write select * except [column type]

You can inspect several properties of the input table's columns in a PTF. This includes its type.

Let's overload the describe function, creating two functions with these parameters:

• One that accepts a list of columns to remove
• One that takes a column type to omit

This gives a package spec like this:

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;  
/

Note that we can also overload the PTF itself by placing it in the package spec.

The implementation of remove-by-type is like remove-by-column. Loop through the table's columns, setting pass_through to false if their type matches the value passed:

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;  
/

Now you can remove all the columns of a specific type from the results like so:

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

DEPARTMENT_NAME               
------------------------------
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
...

Note: parameters that define the shape of the results – that is which columns are in the output – must be constants. If you try and use a bind variable for this, you'll get unexpected results!

For example:

var exclude_type varchar2(30);
exec :exclude_type := 'number';
select * 
from   except_cols_pkg.except_cols ( hr.departments, :exclude_type );

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400

Write select * except [columns] with SQL macros

You can also remove columns from the output using SQL macros. These enable you to create query templates you can pass tables to at runtime.

So – unlike the PTF where you're excluding columns – you're building up a list of the columns you want to include. When iterating through the table's columns, you add columns not in the exclusion list to the query.

For example:

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;
/

As with PTFs, you can also create macros to remove columns by type.

So which should you go for – PTFs or macros?

PTFS vs SQL macros

For the most part which you use comes down to personal preference. Both are transparent to the optimizer, so you should get similar performance from each.

That said, there are a few differences which may sway you one way or another.

To create a PTF, you need to create a package and the function itself. With SQL macros all you need is the function. So with macros you generally have to write less code.

Currently there is a functional difference that may lead you to prefer PTFs though. As it stands you can't use SQL macros inside the with clause, but you can use PTFs here:

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

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
IT
...

When you have multi-stage queries, it’s common to use CTEs to make the SQL easier to read and write. Removing working columns from these interim results is a leading reason for wanting select * except. So you may need to stick with PTFs for now.

Version requirements may also come into play here. PTFs were added in Oracle Database 18c, whereas table SQL macros are first available in 19.6.

But this should pose little concern. Support has ended for 18c, so if you're using this you should be moving to an up-to-date patch of 19c ASAP!

And with 21c available on Oracle Cloud Free Tier and Oracle Database XE, you have free options to try either method.

Summary

You should avoid blanket select * statements in your production code. Using any implementation of select * except as a way to avoid typing out all the columns somewhere in your query is asking for trouble.

That said, this feature can be handy when writing ad-hoc SQL or to remove working columns from multi-stage queries.

By creating a PTF or table SQL macro you can build a generic "remove these columns from the output" function. You could extend the examples above to offer more exclusion options.

Below is a comprehensive macro from Atif Chaudhry, Consulting Member of Technical Staff. It enables you to include or exclude columns by partial name or type.

Head to Live SQL to see this and all the other statements for this post in action.

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;

  sqm := nvl2(sqm, 'select '||rtrim(sqm,',')||' from tab', 'select null from dual');

  if (debug <> 0) then dbms_output.put_line(sqm); end if;

  return sqm;
end;
/