SELECT * EXCEPT using polymorphic table functions

View All Public Scripts Login to Run Script

Script Name SELECT * EXCEPT using polymorphic table functions
Visibility Unlisted - anyone with the share link can access
Description Implementing SELECT * EXCEPT using polymorphic table functions
Area SQL General
Contributor Chris Saxon (Oracle)
Created Wednesday November 10, 2021

Statement 1
There is no SELECT * EXCEPT clause to remove columns from a result set in Oracle SQL. This statement will error
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 2

SQL statements may have several subqueries which add intermediate columns to process the data. For example, this adds the RN column to implement "Top-N/group" functionality. You may want to remove these intermediate columns from the results without having to type out all the table's columns again.

Why SELECT * EXCEPT?

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
122 Payam Kaufling 1
137 Renske Ladwig 2
141 Trenna Rajs 3
105 David Austin 1
103 Alexander Hunold 2
106 Valli Pataballa 3
204 Hermann Baer 1
156 Janette King 1
157 Patrick Sully 2
174 Ellen Abel 3
102 Lex De Haan 1
100 Steven King 2
101 Neena Kochhar 3
109 Daniel Faviet 1
108 Nancy Greenberg 2
110 John Chen 3
205 Shelley Higgins 1
206 William Gietz 2
178 Kimberely Grant 1

26 rows selected.

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
122	Payam	Kaufling	1
137	Renske	Ladwig	2
141	Trenna	Rajs	3
105	David	Austin	1
103	Alexander	Hunold	2
106	Valli	Pataballa	3
204	Hermann	Baer	1
156	Janette	King	1
157	Patrick	Sully	2
174	Ellen	Abel	3
102	Lex	De Haan	1
100	Steven	King	2
101	Neena	Kochhar	3
109	Daniel	Faviet	1
108	Nancy	Greenberg	2
110	John	Chen	3
205	Shelley	Higgins	1
206	William	Gietz	2
178	Kimberely	Grant	1

Statement 3

This defines a describe function with accepts a list of column identifiers to remove from the results

SELECT * EXCEPT polymorphic table function - package 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 4

This loops through the table's columns. If any are in the columns list passed, it removes them from the output.

SELECT * EXCEPT polymorphic table function - package 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 5

This creates the PTF as a stand alone function

SELECT * EXCEPT polymorphic table function

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

Function created.

Statement 6

This removes HIRE_DATE from the query output

Calling the PTF

select *  
from   except_cols ( hr.employees, columns ( hire_date ) )


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
105 David Austin DAUSTIN 590.423.4569 IT_PROG 4800  - 103 60
106 Valli Pataballa VPATABAL 590.423.4560 IT_PROG 4800  - 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 IT_PROG 4200  - 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 FI_MGR 12008  - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 FI_ACCOUNT 9000  - 108 100
110 John Chen JCHEN 515.124.4269 FI_ACCOUNT 8200  - 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 FI_ACCOUNT 7700  - 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 FI_ACCOUNT 7800  - 108 100
113 Luis Popp LPOPP 515.124.4567 FI_ACCOUNT 6900  - 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 PU_MAN 11000  - 100 30
115 Alexander Khoo AKHOO 515.127.4562 PU_CLERK 3100  - 114 30
116 Shelli Baida SBAIDA 515.127.4563 PU_CLERK 2900  - 114 30
117 Sigal Tobias STOBIAS 515.127.4564 PU_CLERK 2800  - 114 30
118 Guy Himuro GHIMURO 515.127.4565 PU_CLERK 2600  - 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 PU_CLERK 2500  - 114 30
120 Matthew Weiss MWEISS 650.123.1234 ST_MAN 8000  - 100 50
121 Adam Fripp AFRIPP 650.123.2234 ST_MAN 8200  - 100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 ST_MAN 7900  - 100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 ST_MAN 6500  - 100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 ST_MAN 5800  - 100 50
125 Julia Nayer JNAYER 650.124.1214 ST_CLERK 3200  - 120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 ST_CLERK 2700  - 120 50
127 James Landry JLANDRY 650.124.1334 ST_CLERK 2400  - 120 50
128 Steven Markle SMARKLE 650.124.1434 ST_CLERK 2200  - 120 50
129 Laura Bissot LBISSOT 650.124.5234 ST_CLERK 3300  - 121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 ST_CLERK 2800  - 121 50
131 James Marlow JAMRLOW 650.124.7234 ST_CLERK 2500  - 121 50
132 TJ Olson TJOLSON 650.124.8234 ST_CLERK 2100  - 121 50
133 Jason Mallin JMALLIN 650.127.1934 ST_CLERK 3300  - 122 50
134 Michael Rogers MROGERS 650.127.1834 ST_CLERK 2900  - 122 50
135 Ki Gee KGEE 650.127.1734 ST_CLERK 2400  - 122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 ST_CLERK 2200  - 122 50
137 Renske Ladwig RLADWIG 650.121.1234 ST_CLERK 3600  - 123 50
138 Stephen Stiles SSTILES 650.121.2034 ST_CLERK 3200  - 123 50
139 John Seo JSEO 650.121.2019 ST_CLERK 2700  - 123 50
140 Joshua Patel JPATEL 650.121.1834 ST_CLERK 2500  - 123 50
141 Trenna Rajs TRAJS 650.121.8009 ST_CLERK 3500  - 124 50
142 Curtis Davies CDAVIES 650.121.2994 ST_CLERK 3100  - 124 50
143 Randall Matos RMATOS 650.121.2874 ST_CLERK 2600  - 124 50
144 Peter Vargas PVARGAS 650.121.2004 ST_CLERK 2500  - 124 50
145 John Russell JRUSSEL 011.44.1344.429268 SA_MAN 14000 .4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 SA_MAN 13500 .3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 SA_MAN 12000 .3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 SA_MAN 11000 .3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 SA_MAN 10500 .2 100 80

Rows 1 - 50. 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
105	David	Austin	DAUSTIN	590.423.4569	IT_PROG	4800	-	103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	IT_PROG	4800	-	103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	IT_PROG	4200	-	103	60
108	Nancy	Greenberg	NGREENBE	515.124.4569	FI_MGR	12008	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	FI_ACCOUNT	9000	-	108	100
110	John	Chen	JCHEN	515.124.4269	FI_ACCOUNT	8200	-	108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	FI_ACCOUNT	7700	-	108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	FI_ACCOUNT	7800	-	108	100
113	Luis	Popp	LPOPP	515.124.4567	FI_ACCOUNT	6900	-	108	100
114	Den	Raphaely	DRAPHEAL	515.127.4561	PU_MAN	11000	-	100	30
115	Alexander	Khoo	AKHOO	515.127.4562	PU_CLERK	3100	-	114	30
116	Shelli	Baida	SBAIDA	515.127.4563	PU_CLERK	2900	-	114	30
117	Sigal	Tobias	STOBIAS	515.127.4564	PU_CLERK	2800	-	114	30
118	Guy	Himuro	GHIMURO	515.127.4565	PU_CLERK	2600	-	114	30
119	Karen	Colmenares	KCOLMENA	515.127.4566	PU_CLERK	2500	-	114	30
120	Matthew	Weiss	MWEISS	650.123.1234	ST_MAN	8000	-	100	50
121	Adam	Fripp	AFRIPP	650.123.2234	ST_MAN	8200	-	100	50
122	Payam	Kaufling	PKAUFLIN	650.123.3234	ST_MAN	7900	-	100	50
123	Shanta	Vollman	SVOLLMAN	650.123.4234	ST_MAN	6500	-	100	50
124	Kevin	Mourgos	KMOURGOS	650.123.5234	ST_MAN	5800	-	100	50
125	Julia	Nayer	JNAYER	650.124.1214	ST_CLERK	3200	-	120	50
126	Irene	Mikkilineni	IMIKKILI	650.124.1224	ST_CLERK	2700	-	120	50
127	James	Landry	JLANDRY	650.124.1334	ST_CLERK	2400	-	120	50
128	Steven	Markle	SMARKLE	650.124.1434	ST_CLERK	2200	-	120	50
129	Laura	Bissot	LBISSOT	650.124.5234	ST_CLERK	3300	-	121	50
130	Mozhe	Atkinson	MATKINSO	650.124.6234	ST_CLERK	2800	-	121	50
131	James	Marlow	JAMRLOW	650.124.7234	ST_CLERK	2500	-	121	50
132	TJ	Olson	TJOLSON	650.124.8234	ST_CLERK	2100	-	121	50
133	Jason	Mallin	JMALLIN	650.127.1934	ST_CLERK	3300	-	122	50
134	Michael	Rogers	MROGERS	650.127.1834	ST_CLERK	2900	-	122	50
135	Ki	Gee	KGEE	650.127.1734	ST_CLERK	2400	-	122	50
136	Hazel	Philtanker	HPHILTAN	650.127.1634	ST_CLERK	2200	-	122	50
137	Renske	Ladwig	RLADWIG	650.121.1234	ST_CLERK	3600	-	123	50
138	Stephen	Stiles	SSTILES	650.121.2034	ST_CLERK	3200	-	123	50
139	John	Seo	JSEO	650.121.2019	ST_CLERK	2700	-	123	50
140	Joshua	Patel	JPATEL	650.121.1834	ST_CLERK	2500	-	123	50
141	Trenna	Rajs	TRAJS	650.121.8009	ST_CLERK	3500	-	124	50
142	Curtis	Davies	CDAVIES	650.121.2994	ST_CLERK	3100	-	124	50
143	Randall	Matos	RMATOS	650.121.2874	ST_CLERK	2600	-	124	50
144	Peter	Vargas	PVARGAS	650.121.2004	ST_CLERK	2500	-	124	50
145	John	Russell	JRUSSEL	011.44.1344.429268	SA_MAN	14000	.4	100	80
146	Karen	Partners	KPARTNER	011.44.1344.467268	SA_MAN	13500	.3	100	80
147	Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	SA_MAN	12000	.3	100	80
148	Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	SA_MAN	11000	.3	100	80
149	Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	SA_MAN	10500	.2	100	80

Statement 7

You can pass a list of many columns to exclude from the results

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
David Austin DAUSTIN 590.423.4569 25-JUN-05 60
Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 60
Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 60
Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 100
Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 100
John Chen JCHEN 515.124.4269 28-SEP-05 100
Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 100
Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 100
Luis Popp LPOPP 515.124.4567 07-DEC-07 100
Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 30
Alexander Khoo AKHOO 515.127.4562 18-MAY-03 30
Shelli Baida SBAIDA 515.127.4563 24-DEC-05 30
Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 30
Guy Himuro GHIMURO 515.127.4565 15-NOV-06 30
Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 30
Matthew Weiss MWEISS 650.123.1234 18-JUL-04 50
Adam Fripp AFRIPP 650.123.2234 10-APR-05 50
Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 50
Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 50
Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 50
Julia Nayer JNAYER 650.124.1214 16-JUL-05 50
Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 50
James Landry JLANDRY 650.124.1334 14-JAN-07 50
Steven Markle SMARKLE 650.124.1434 08-MAR-08 50
Laura Bissot LBISSOT 650.124.5234 20-AUG-05 50
Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 50
James Marlow JAMRLOW 650.124.7234 16-FEB-05 50
TJ Olson TJOLSON 650.124.8234 10-APR-07 50
Jason Mallin JMALLIN 650.127.1934 14-JUN-04 50
Michael Rogers MROGERS 650.127.1834 26-AUG-06 50
Ki Gee KGEE 650.127.1734 12-DEC-07 50
Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 50
Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 50
Stephen Stiles SSTILES 650.121.2034 26-OCT-05 50
John Seo JSEO 650.121.2019 12-FEB-06 50
Joshua Patel JPATEL 650.121.1834 06-APR-06 50
Trenna Rajs TRAJS 650.121.8009 17-OCT-03 50
Curtis Davies CDAVIES 650.121.2994 29-JAN-05 50
Randall Matos RMATOS 650.121.2874 15-MAR-06 50
Peter Vargas PVARGAS 650.121.2004 09-JUL-06 50
John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 80
Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 80
Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 80
Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 80
Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 80

Rows 1 - 50. 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
David	Austin	DAUSTIN	590.423.4569	25-JUN-05	60
Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	60
Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	60
Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	100
Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	100
John	Chen	JCHEN	515.124.4269	28-SEP-05	100
Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	100
Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	100
Luis	Popp	LPOPP	515.124.4567	07-DEC-07	100
Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	30
Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	30
Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	30
Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	30
Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	30
Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	30
Matthew	Weiss	MWEISS	650.123.1234	18-JUL-04	50
Adam	Fripp	AFRIPP	650.123.2234	10-APR-05	50
Payam	Kaufling	PKAUFLIN	650.123.3234	01-MAY-03	50
Shanta	Vollman	SVOLLMAN	650.123.4234	10-OCT-05	50
Kevin	Mourgos	KMOURGOS	650.123.5234	16-NOV-07	50
Julia	Nayer	JNAYER	650.124.1214	16-JUL-05	50
Irene	Mikkilineni	IMIKKILI	650.124.1224	28-SEP-06	50
James	Landry	JLANDRY	650.124.1334	14-JAN-07	50
Steven	Markle	SMARKLE	650.124.1434	08-MAR-08	50
Laura	Bissot	LBISSOT	650.124.5234	20-AUG-05	50
Mozhe	Atkinson	MATKINSO	650.124.6234	30-OCT-05	50
James	Marlow	JAMRLOW	650.124.7234	16-FEB-05	50
TJ	Olson	TJOLSON	650.124.8234	10-APR-07	50
Jason	Mallin	JMALLIN	650.127.1934	14-JUN-04	50
Michael	Rogers	MROGERS	650.127.1834	26-AUG-06	50
Ki	Gee	KGEE	650.127.1734	12-DEC-07	50
Hazel	Philtanker	HPHILTAN	650.127.1634	06-FEB-08	50
Renske	Ladwig	RLADWIG	650.121.1234	14-JUL-03	50
Stephen	Stiles	SSTILES	650.121.2034	26-OCT-05	50
John	Seo	JSEO	650.121.2019	12-FEB-06	50
Joshua	Patel	JPATEL	650.121.1834	06-APR-06	50
Trenna	Rajs	TRAJS	650.121.8009	17-OCT-03	50
Curtis	Davies	CDAVIES	650.121.2994	29-JAN-05	50
Randall	Matos	RMATOS	650.121.2874	15-MAR-06	50
Peter	Vargas	PVARGAS	650.121.2004	09-JUL-06	50
John	Russell	JRUSSEL	011.44.1344.429268	01-OCT-04	80
Karen	Partners	KPARTNER	011.44.1344.467268	05-JAN-05	80
Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	10-MAR-05	80
Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	15-OCT-07	80
Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	29-JAN-08	80

Statement 8

This functionality works on any table

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
IT 103 1400
Public Relations 204 2700
Sales 145 2500
Executive 100 1700
Finance 108 1700
Accounting 205 1700
Treasury  - 1700
Corporate Tax  - 1700
Control And Credit  - 1700
Shareholder Services  - 1700
Benefits  - 1700
Manufacturing  - 1700
Construction  - 1700
Contracting  - 1700
Operations  - 1700
IT Support  - 1700
NOC  - 1700
IT Helpdesk  - 1700
Government Sales  - 1700
Retail Sales  - 1700
Recruiting  - 1700
Payroll  - 1700

27 rows selected.

Statement 9
This removes all the columns from the results - so the statement errors!
Removing every column
```
select * from except_cols ( dual, columns ( dummy ) )
```
```
ORA-30732: table contains no user-visible columns
```
More Details: https://docs.oracle.com/error-help/db/ora-30732
Statement 10
This passes "dummy", so searches for a column with the lowercase name dummy in DUAL. There is no such column, so the output is unchanged.
Column names must match
```
select * from except_cols ( dual, columns ( "dummy" ) )
```
```
DUMMY
X
```

DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
Administration	200	1700
Marketing	201	1800
Purchasing	114	1700
Human Resources	203	2400
Shipping	121	1500
IT	103	1400
Public Relations	204	2700
Sales	145	2500
Executive	100	1700
Finance	108	1700
Accounting	205	1700
Treasury	-	1700
Corporate Tax	-	1700
Control And Credit	-	1700
Shareholder Services	-	1700
Benefits	-	1700
Manufacturing	-	1700
Construction	-	1700
Contracting	-	1700
Operations	-	1700
IT Support	-	1700
NOC	-	1700
IT Helpdesk	-	1700
Government Sales	-	1700
Retail Sales	-	1700
Recruiting	-	1700
Payroll	-	1700

DUMMY
X

Statement 11

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 * /* What's going to happen? */ 
  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 12

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 
), 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
200 Jennifer Whalen
201 Michael Hartstein
202 Pat Fay
114 Den Raphaely
115 Alexander Khoo
117 Sigal Tobias
203 Susan Mavris
122 Payam Kaufling
137 Renske Ladwig
141 Trenna Rajs
105 David Austin
103 Alexander Hunold
106 Valli Pataballa
204 Hermann Baer
156 Janette King
157 Patrick Sully
174 Ellen Abel
102 Lex De Haan
100 Steven King
101 Neena Kochhar
109 Daniel Faviet
108 Nancy Greenberg
110 John Chen
205 Shelley Higgins
206 William Gietz
178 Kimberely Grant

26 rows selected.

EMPLOYEE_ID	FIRST_NAME	LAST_NAME
200	Jennifer	Whalen
201	Michael	Hartstein
202	Pat	Fay
114	Den	Raphaely
115	Alexander	Khoo
117	Sigal	Tobias
203	Susan	Mavris
122	Payam	Kaufling
137	Renske	Ladwig
141	Trenna	Rajs
105	David	Austin
103	Alexander	Hunold
106	Valli	Pataballa
204	Hermann	Baer
156	Janette	King
157	Patrick	Sully
174	Ellen	Abel
102	Lex	De Haan
100	Steven	King
101	Neena	Kochhar
109	Daniel	Faviet
108	Nancy	Greenberg
110	John	Chen
205	Shelley	Higgins
206	William	Gietz
178	Kimberely	Grant

Statement 13

This creates two versions of the PTF: one accepting a list of column types, another which removes all columns of a specific data type. To allow overloading of the PTF itself, these functions are defined in the package.

Overloading the PTF - package 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 14

This adds the implementation for "remove column by type" to the package. Note that the PTFs themselves have no corresponding function - they are bound to the package.

Overloading the PTF - package 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 15

This removes columns by name

Using an overloaded PTF

select *  
from   except_cols_pkg.except_cols ( hr.employees, columns ( hire_date ) )


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
105 David Austin DAUSTIN 590.423.4569 IT_PROG 4800  - 103 60
106 Valli Pataballa VPATABAL 590.423.4560 IT_PROG 4800  - 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 IT_PROG 4200  - 103 60
108 Nancy Greenberg NGREENBE 515.124.4569 FI_MGR 12008  - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 FI_ACCOUNT 9000  - 108 100
110 John Chen JCHEN 515.124.4269 FI_ACCOUNT 8200  - 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 FI_ACCOUNT 7700  - 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 FI_ACCOUNT 7800  - 108 100
113 Luis Popp LPOPP 515.124.4567 FI_ACCOUNT 6900  - 108 100
114 Den Raphaely DRAPHEAL 515.127.4561 PU_MAN 11000  - 100 30
115 Alexander Khoo AKHOO 515.127.4562 PU_CLERK 3100  - 114 30
116 Shelli Baida SBAIDA 515.127.4563 PU_CLERK 2900  - 114 30
117 Sigal Tobias STOBIAS 515.127.4564 PU_CLERK 2800  - 114 30
118 Guy Himuro GHIMURO 515.127.4565 PU_CLERK 2600  - 114 30
119 Karen Colmenares KCOLMENA 515.127.4566 PU_CLERK 2500  - 114 30
120 Matthew Weiss MWEISS 650.123.1234 ST_MAN 8000  - 100 50
121 Adam Fripp AFRIPP 650.123.2234 ST_MAN 8200  - 100 50
122 Payam Kaufling PKAUFLIN 650.123.3234 ST_MAN 7900  - 100 50
123 Shanta Vollman SVOLLMAN 650.123.4234 ST_MAN 6500  - 100 50
124 Kevin Mourgos KMOURGOS 650.123.5234 ST_MAN 5800  - 100 50
125 Julia Nayer JNAYER 650.124.1214 ST_CLERK 3200  - 120 50
126 Irene Mikkilineni IMIKKILI 650.124.1224 ST_CLERK 2700  - 120 50
127 James Landry JLANDRY 650.124.1334 ST_CLERK 2400  - 120 50
128 Steven Markle SMARKLE 650.124.1434 ST_CLERK 2200  - 120 50
129 Laura Bissot LBISSOT 650.124.5234 ST_CLERK 3300  - 121 50
130 Mozhe Atkinson MATKINSO 650.124.6234 ST_CLERK 2800  - 121 50
131 James Marlow JAMRLOW 650.124.7234 ST_CLERK 2500  - 121 50
132 TJ Olson TJOLSON 650.124.8234 ST_CLERK 2100  - 121 50
133 Jason Mallin JMALLIN 650.127.1934 ST_CLERK 3300  - 122 50
134 Michael Rogers MROGERS 650.127.1834 ST_CLERK 2900  - 122 50
135 Ki Gee KGEE 650.127.1734 ST_CLERK 2400  - 122 50
136 Hazel Philtanker HPHILTAN 650.127.1634 ST_CLERK 2200  - 122 50
137 Renske Ladwig RLADWIG 650.121.1234 ST_CLERK 3600  - 123 50
138 Stephen Stiles SSTILES 650.121.2034 ST_CLERK 3200  - 123 50
139 John Seo JSEO 650.121.2019 ST_CLERK 2700  - 123 50
140 Joshua Patel JPATEL 650.121.1834 ST_CLERK 2500  - 123 50
141 Trenna Rajs TRAJS 650.121.8009 ST_CLERK 3500  - 124 50
142 Curtis Davies CDAVIES 650.121.2994 ST_CLERK 3100  - 124 50
143 Randall Matos RMATOS 650.121.2874 ST_CLERK 2600  - 124 50
144 Peter Vargas PVARGAS 650.121.2004 ST_CLERK 2500  - 124 50
145 John Russell JRUSSEL 011.44.1344.429268 SA_MAN 14000 .4 100 80
146 Karen Partners KPARTNER 011.44.1344.467268 SA_MAN 13500 .3 100 80
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 SA_MAN 12000 .3 100 80
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 SA_MAN 11000 .3 100 80
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 SA_MAN 10500 .2 100 80

Rows 1 - 50. 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
105	David	Austin	DAUSTIN	590.423.4569	IT_PROG	4800	-	103	60
106	Valli	Pataballa	VPATABAL	590.423.4560	IT_PROG	4800	-	103	60
107	Diana	Lorentz	DLORENTZ	590.423.5567	IT_PROG	4200	-	103	60
108	Nancy	Greenberg	NGREENBE	515.124.4569	FI_MGR	12008	-	101	100
109	Daniel	Faviet	DFAVIET	515.124.4169	FI_ACCOUNT	9000	-	108	100
110	John	Chen	JCHEN	515.124.4269	FI_ACCOUNT	8200	-	108	100
111	Ismael	Sciarra	ISCIARRA	515.124.4369	FI_ACCOUNT	7700	-	108	100
112	Jose Manuel	Urman	JMURMAN	515.124.4469	FI_ACCOUNT	7800	-	108	100
113	Luis	Popp	LPOPP	515.124.4567	FI_ACCOUNT	6900	-	108	100
114	Den	Raphaely	DRAPHEAL	515.127.4561	PU_MAN	11000	-	100	30
115	Alexander	Khoo	AKHOO	515.127.4562	PU_CLERK	3100	-	114	30
116	Shelli	Baida	SBAIDA	515.127.4563	PU_CLERK	2900	-	114	30
117	Sigal	Tobias	STOBIAS	515.127.4564	PU_CLERK	2800	-	114	30
118	Guy	Himuro	GHIMURO	515.127.4565	PU_CLERK	2600	-	114	30
119	Karen	Colmenares	KCOLMENA	515.127.4566	PU_CLERK	2500	-	114	30
120	Matthew	Weiss	MWEISS	650.123.1234	ST_MAN	8000	-	100	50
121	Adam	Fripp	AFRIPP	650.123.2234	ST_MAN	8200	-	100	50
122	Payam	Kaufling	PKAUFLIN	650.123.3234	ST_MAN	7900	-	100	50
123	Shanta	Vollman	SVOLLMAN	650.123.4234	ST_MAN	6500	-	100	50
124	Kevin	Mourgos	KMOURGOS	650.123.5234	ST_MAN	5800	-	100	50
125	Julia	Nayer	JNAYER	650.124.1214	ST_CLERK	3200	-	120	50
126	Irene	Mikkilineni	IMIKKILI	650.124.1224	ST_CLERK	2700	-	120	50
127	James	Landry	JLANDRY	650.124.1334	ST_CLERK	2400	-	120	50
128	Steven	Markle	SMARKLE	650.124.1434	ST_CLERK	2200	-	120	50
129	Laura	Bissot	LBISSOT	650.124.5234	ST_CLERK	3300	-	121	50
130	Mozhe	Atkinson	MATKINSO	650.124.6234	ST_CLERK	2800	-	121	50
131	James	Marlow	JAMRLOW	650.124.7234	ST_CLERK	2500	-	121	50
132	TJ	Olson	TJOLSON	650.124.8234	ST_CLERK	2100	-	121	50
133	Jason	Mallin	JMALLIN	650.127.1934	ST_CLERK	3300	-	122	50
134	Michael	Rogers	MROGERS	650.127.1834	ST_CLERK	2900	-	122	50
135	Ki	Gee	KGEE	650.127.1734	ST_CLERK	2400	-	122	50
136	Hazel	Philtanker	HPHILTAN	650.127.1634	ST_CLERK	2200	-	122	50
137	Renske	Ladwig	RLADWIG	650.121.1234	ST_CLERK	3600	-	123	50
138	Stephen	Stiles	SSTILES	650.121.2034	ST_CLERK	3200	-	123	50
139	John	Seo	JSEO	650.121.2019	ST_CLERK	2700	-	123	50
140	Joshua	Patel	JPATEL	650.121.1834	ST_CLERK	2500	-	123	50
141	Trenna	Rajs	TRAJS	650.121.8009	ST_CLERK	3500	-	124	50
142	Curtis	Davies	CDAVIES	650.121.2994	ST_CLERK	3100	-	124	50
143	Randall	Matos	RMATOS	650.121.2874	ST_CLERK	2600	-	124	50
144	Peter	Vargas	PVARGAS	650.121.2004	ST_CLERK	2500	-	124	50
145	John	Russell	JRUSSEL	011.44.1344.429268	SA_MAN	14000	.4	100	80
146	Karen	Partners	KPARTNER	011.44.1344.467268	SA_MAN	13500	.3	100	80
147	Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	SA_MAN	12000	.3	100	80
148	Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	SA_MAN	11000	.3	100	80
149	Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	SA_MAN	10500	.2	100	80

Statement 16

This removes all numeric columns from the EMPLOYEES table

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


FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
Steven King SKING 515.123.4567 17-JUN-03 AD_PRES
Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP
Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP
Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG
Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG
David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG
Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG
Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG
Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR
Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT
John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT
Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT
Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT
Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT
Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN
Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK
Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK
Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK
Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK
Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK
Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN
Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN
Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN
Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN
Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN
Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK
Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK
James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK
Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK
Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK
Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK
James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK
TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK
Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK
Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK
Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK
Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK
Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK
Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK
John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK
Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK
Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK
Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK
Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK
Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK
John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN
Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN
Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN
Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN
Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN

Rows 1 - 50. More rows exist.

FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID
Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES
Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP
Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP
Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG
Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG
David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG
Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG
Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG
Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR
Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT
John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT
Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT
Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT
Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT
Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN
Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK
Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK
Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK
Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK
Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK
Matthew	Weiss	MWEISS	650.123.1234	18-JUL-04	ST_MAN
Adam	Fripp	AFRIPP	650.123.2234	10-APR-05	ST_MAN
Payam	Kaufling	PKAUFLIN	650.123.3234	01-MAY-03	ST_MAN
Shanta	Vollman	SVOLLMAN	650.123.4234	10-OCT-05	ST_MAN
Kevin	Mourgos	KMOURGOS	650.123.5234	16-NOV-07	ST_MAN
Julia	Nayer	JNAYER	650.124.1214	16-JUL-05	ST_CLERK
Irene	Mikkilineni	IMIKKILI	650.124.1224	28-SEP-06	ST_CLERK
James	Landry	JLANDRY	650.124.1334	14-JAN-07	ST_CLERK
Steven	Markle	SMARKLE	650.124.1434	08-MAR-08	ST_CLERK
Laura	Bissot	LBISSOT	650.124.5234	20-AUG-05	ST_CLERK
Mozhe	Atkinson	MATKINSO	650.124.6234	30-OCT-05	ST_CLERK
James	Marlow	JAMRLOW	650.124.7234	16-FEB-05	ST_CLERK
TJ	Olson	TJOLSON	650.124.8234	10-APR-07	ST_CLERK
Jason	Mallin	JMALLIN	650.127.1934	14-JUN-04	ST_CLERK
Michael	Rogers	MROGERS	650.127.1834	26-AUG-06	ST_CLERK
Ki	Gee	KGEE	650.127.1734	12-DEC-07	ST_CLERK
Hazel	Philtanker	HPHILTAN	650.127.1634	06-FEB-08	ST_CLERK
Renske	Ladwig	RLADWIG	650.121.1234	14-JUL-03	ST_CLERK
Stephen	Stiles	SSTILES	650.121.2034	26-OCT-05	ST_CLERK
John	Seo	JSEO	650.121.2019	12-FEB-06	ST_CLERK
Joshua	Patel	JPATEL	650.121.1834	06-APR-06	ST_CLERK
Trenna	Rajs	TRAJS	650.121.8009	17-OCT-03	ST_CLERK
Curtis	Davies	CDAVIES	650.121.2994	29-JAN-05	ST_CLERK
Randall	Matos	RMATOS	650.121.2874	15-MAR-06	ST_CLERK
Peter	Vargas	PVARGAS	650.121.2004	09-JUL-06	ST_CLERK
John	Russell	JRUSSEL	011.44.1344.429268	01-OCT-04	SA_MAN
Karen	Partners	KPARTNER	011.44.1344.467268	05-JAN-05	SA_MAN
Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	10-MAR-05	SA_MAN
Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	15-OCT-07	SA_MAN
Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	29-JAN-08	SA_MAN

Statement 17

select * from co.products


PRODUCT_ID PRODUCT_NAME UNIT_PRICE PRODUCT_DETAILS PRODUCT_IMAGE IMAGE_MIME_TYPE IMAGE_FILENAME IMAGE_CHARSET IMAGE_LAST_UPDATED
36 Women's Trousers (Blue) 29.51 [unsupported data type] [unsupported data type]  -  -  -  - 
37 Boy's Jeans (Blue) 22.98 [unsupported data type] [unsupported data type]  -  -  -  - 
38 Girl's Pyjamas (Red) 11 [unsupported data type] [unsupported data type]  -  -  -  - 
39 Boy's Trousers (Blue) 34.06 [unsupported data type] [unsupported data type]  -  -  -  - 
40 Girl's Pyjamas (Black) 8.66 [unsupported data type] [unsupported data type]  -  -  -  - 
41 Women's Dress (Black) 10.11 [unsupported data type] [unsupported data type]  -  -  -  - 
42 Boy's Jeans (Black) 16.64 [unsupported data type] [unsupported data type]  -  -  -  - 
43 Boy's Trousers (Black) 39.32 [unsupported data type] [unsupported data type]  -  -  -  - 
1 Boy's Shirt (White) 29.55 [unsupported data type] [unsupported data type]  -  -  -  - 
2 Women's Shirt (Green) 16.67 [unsupported data type] [unsupported data type]  -  -  -  - 
3 Boy's Sweater (Green) 44.17 [unsupported data type] [unsupported data type]  -  -  -  - 
4 Boy's Trousers (White) 43.71 [unsupported data type] [unsupported data type]  -  -  -  - 
5 Girl's Shorts (Red) 38.28 [unsupported data type] [unsupported data type]  -  -  -  - 
6 Boy's Socks (Grey) 19.16 [unsupported data type] [unsupported data type]  -  -  -  - 
7 Boy's Socks (Black) 19.58 [unsupported data type] [unsupported data type]  -  -  -  - 
8 Boy's Coat (Brown) 21.16 [unsupported data type] [unsupported data type]  -  -  -  - 
9 Women's Jeans (Brown) 29.49 [unsupported data type] [unsupported data type]  -  -  -  - 
10 Women's Skirt (Red) 30.69 [unsupported data type] [unsupported data type]  -  -  -  - 
11 Boy's Shorts (Blue) 10.48 [unsupported data type] [unsupported data type]  -  -  -  - 
12 Boy's Socks (White) 12.64 [unsupported data type] [unsupported data type]  -  -  -  - 
13 Boy's Hoodie (Grey) 26.14 [unsupported data type] [unsupported data type]  -  -  -  - 
14 Women's Skirt (Brown) 13.97 [unsupported data type] [unsupported data type]  -  -  -  - 
15 Girl's Coat (Blue) 13.09 [unsupported data type] [unsupported data type]  -  -  -  - 
16 Women's Socks (Grey) 39.89 [unsupported data type] [unsupported data type]  -  -  -  - 
17 Women's Sweater (Brown) 24.46 [unsupported data type] [unsupported data type]  -  -  -  - 
18 Women's Jacket (Black) 14.34 [unsupported data type] [unsupported data type]  -  -  -  - 
19 Men's Coat (Red) 28.21 [unsupported data type] [unsupported data type]  -  -  -  - 
20 Girl's Shorts (Green) 38.34 [unsupported data type] [unsupported data type]  -  -  -  - 
21 Girl's Pyjamas (White) 39.78 [unsupported data type] [unsupported data type]  -  -  -  - 
22 Men's Shorts (Black) 10.33 [unsupported data type] [unsupported data type]  -  -  -  - 
23 Men's Pyjamas (Blue) 48.39 [unsupported data type] [unsupported data type]  -  -  -  - 
24 Boy's Sweater (Red) 9.8 [unsupported data type] [unsupported data type]  -  -  -  - 
25 Girl's Jeans (Grey) 48.75 [unsupported data type] [unsupported data type]  -  -  -  - 
26 Girl's Hoodie (White) 39.91 [unsupported data type] [unsupported data type]  -  -  -  - 
27 Boy's Coat (Blue) 10.24 [unsupported data type] [unsupported data type]  -  -  -  - 
28 Men's Hoodie (Red) 24.71 [unsupported data type] [unsupported data type]  -  -  -  - 
29 Boy's Shirt (Black) 37.34 [unsupported data type] [unsupported data type]  -  -  -  - 
30 Women's Pyjamas (Grey) 28.59 [unsupported data type] [unsupported data type]  -  -  -  - 
31 Women's Skirt (Green) 5.65 [unsupported data type] [unsupported data type]  -  -  -  - 
32 Women's Jacket (Blue) 37 [unsupported data type] [unsupported data type]  -  -  -  - 
33 Boy's Pyjamas (Grey) 23.32 [unsupported data type] [unsupported data type]  -  -  -  - 
34 Women's Jeans (Red) 7.18 [unsupported data type] [unsupported data type]  -  -  -  - 
35 Girl's Dress (Red) 49.12 [unsupported data type] [unsupported data type]  -  -  -  - 
44 Women's Coat (Black) 31.68 [unsupported data type] [unsupported data type]  -  -  -  - 
45 Men's Jeans (Grey) 27.64 [unsupported data type] [unsupported data type]  -  -  -  - 
46 Girl's Trousers (Red) 39.16 [unsupported data type] [unsupported data type]  -  -  -  - 

46 rows selected.

PRODUCT_ID	PRODUCT_NAME	UNIT_PRICE	PRODUCT_DETAILS	PRODUCT_IMAGE	IMAGE_MIME_TYPE	IMAGE_FILENAME	IMAGE_CHARSET	IMAGE_LAST_UPDATED
36	Women's Trousers (Blue)	29.51	[unsupported data type]	[unsupported data type]	-	-	-	-
37	Boy's Jeans (Blue)	22.98	[unsupported data type]	[unsupported data type]	-	-	-	-
38	Girl's Pyjamas (Red)	11	[unsupported data type]	[unsupported data type]	-	-	-	-
39	Boy's Trousers (Blue)	34.06	[unsupported data type]	[unsupported data type]	-	-	-	-
40	Girl's Pyjamas (Black)	8.66	[unsupported data type]	[unsupported data type]	-	-	-	-
41	Women's Dress (Black)	10.11	[unsupported data type]	[unsupported data type]	-	-	-	-
42	Boy's Jeans (Black)	16.64	[unsupported data type]	[unsupported data type]	-	-	-	-
43	Boy's Trousers (Black)	39.32	[unsupported data type]	[unsupported data type]	-	-	-	-
1	Boy's Shirt (White)	29.55	[unsupported data type]	[unsupported data type]	-	-	-	-
2	Women's Shirt (Green)	16.67	[unsupported data type]	[unsupported data type]	-	-	-	-
3	Boy's Sweater (Green)	44.17	[unsupported data type]	[unsupported data type]	-	-	-	-
4	Boy's Trousers (White)	43.71	[unsupported data type]	[unsupported data type]	-	-	-	-
5	Girl's Shorts (Red)	38.28	[unsupported data type]	[unsupported data type]	-	-	-	-
6	Boy's Socks (Grey)	19.16	[unsupported data type]	[unsupported data type]	-	-	-	-
7	Boy's Socks (Black)	19.58	[unsupported data type]	[unsupported data type]	-	-	-	-
8	Boy's Coat (Brown)	21.16	[unsupported data type]	[unsupported data type]	-	-	-	-
9	Women's Jeans (Brown)	29.49	[unsupported data type]	[unsupported data type]	-	-	-	-
10	Women's Skirt (Red)	30.69	[unsupported data type]	[unsupported data type]	-	-	-	-
11	Boy's Shorts (Blue)	10.48	[unsupported data type]	[unsupported data type]	-	-	-	-
12	Boy's Socks (White)	12.64	[unsupported data type]	[unsupported data type]	-	-	-	-
13	Boy's Hoodie (Grey)	26.14	[unsupported data type]	[unsupported data type]	-	-	-	-
14	Women's Skirt (Brown)	13.97	[unsupported data type]	[unsupported data type]	-	-	-	-
15	Girl's Coat (Blue)	13.09	[unsupported data type]	[unsupported data type]	-	-	-	-
16	Women's Socks (Grey)	39.89	[unsupported data type]	[unsupported data type]	-	-	-	-
17	Women's Sweater (Brown)	24.46	[unsupported data type]	[unsupported data type]	-	-	-	-
18	Women's Jacket (Black)	14.34	[unsupported data type]	[unsupported data type]	-	-	-	-
19	Men's Coat (Red)	28.21	[unsupported data type]	[unsupported data type]	-	-	-	-
20	Girl's Shorts (Green)	38.34	[unsupported data type]	[unsupported data type]	-	-	-	-
21	Girl's Pyjamas (White)	39.78	[unsupported data type]	[unsupported data type]	-	-	-	-
22	Men's Shorts (Black)	10.33	[unsupported data type]	[unsupported data type]	-	-	-	-
23	Men's Pyjamas (Blue)	48.39	[unsupported data type]	[unsupported data type]	-	-	-	-
24	Boy's Sweater (Red)	9.8	[unsupported data type]	[unsupported data type]	-	-	-	-
25	Girl's Jeans (Grey)	48.75	[unsupported data type]	[unsupported data type]	-	-	-	-
26	Girl's Hoodie (White)	39.91	[unsupported data type]	[unsupported data type]	-	-	-	-
27	Boy's Coat (Blue)	10.24	[unsupported data type]	[unsupported data type]	-	-	-	-
28	Men's Hoodie (Red)	24.71	[unsupported data type]	[unsupported data type]	-	-	-	-
29	Boy's Shirt (Black)	37.34	[unsupported data type]	[unsupported data type]	-	-	-	-
30	Women's Pyjamas (Grey)	28.59	[unsupported data type]	[unsupported data type]	-	-	-	-
31	Women's Skirt (Green)	5.65	[unsupported data type]	[unsupported data type]	-	-	-	-
32	Women's Jacket (Blue)	37	[unsupported data type]	[unsupported data type]	-	-	-	-
33	Boy's Pyjamas (Grey)	23.32	[unsupported data type]	[unsupported data type]	-	-	-	-
34	Women's Jeans (Red)	7.18	[unsupported data type]	[unsupported data type]	-	-	-	-
35	Girl's Dress (Red)	49.12	[unsupported data type]	[unsupported data type]	-	-	-	-
44	Women's Coat (Black)	31.68	[unsupported data type]	[unsupported data type]	-	-	-	-
45	Men's Jeans (Grey)	27.64	[unsupported data type]	[unsupported data type]	-	-	-	-
46	Girl's Trousers (Red)	39.16	[unsupported data type]	[unsupported data type]	-	-	-	-

Statement 18

Remove all the BLOB columns from the results

select *  
from   except_cols_pkg.except_cols ( co.products, 'blob' )


PRODUCT_ID PRODUCT_NAME UNIT_PRICE IMAGE_MIME_TYPE IMAGE_FILENAME IMAGE_CHARSET IMAGE_LAST_UPDATED
36 Women's Trousers (Blue) 29.51  -  -  -  - 
37 Boy's Jeans (Blue) 22.98  -  -  -  - 
38 Girl's Pyjamas (Red) 11  -  -  -  - 
39 Boy's Trousers (Blue) 34.06  -  -  -  - 
40 Girl's Pyjamas (Black) 8.66  -  -  -  - 
41 Women's Dress (Black) 10.11  -  -  -  - 
42 Boy's Jeans (Black) 16.64  -  -  -  - 
43 Boy's Trousers (Black) 39.32  -  -  -  - 
1 Boy's Shirt (White) 29.55  -  -  -  - 
2 Women's Shirt (Green) 16.67  -  -  -  - 
3 Boy's Sweater (Green) 44.17  -  -  -  - 
4 Boy's Trousers (White) 43.71  -  -  -  - 
5 Girl's Shorts (Red) 38.28  -  -  -  - 
6 Boy's Socks (Grey) 19.16  -  -  -  - 
7 Boy's Socks (Black) 19.58  -  -  -  - 
8 Boy's Coat (Brown) 21.16  -  -  -  - 
9 Women's Jeans (Brown) 29.49  -  -  -  - 
10 Women's Skirt (Red) 30.69  -  -  -  - 
11 Boy's Shorts (Blue) 10.48  -  -  -  - 
12 Boy's Socks (White) 12.64  -  -  -  - 
13 Boy's Hoodie (Grey) 26.14  -  -  -  - 
14 Women's Skirt (Brown) 13.97  -  -  -  - 
15 Girl's Coat (Blue) 13.09  -  -  -  - 
16 Women's Socks (Grey) 39.89  -  -  -  - 
17 Women's Sweater (Brown) 24.46  -  -  -  - 
18 Women's Jacket (Black) 14.34  -  -  -  - 
19 Men's Coat (Red) 28.21  -  -  -  - 
20 Girl's Shorts (Green) 38.34  -  -  -  - 
21 Girl's Pyjamas (White) 39.78  -  -  -  - 
22 Men's Shorts (Black) 10.33  -  -  -  - 
23 Men's Pyjamas (Blue) 48.39  -  -  -  - 
24 Boy's Sweater (Red) 9.8  -  -  -  - 
25 Girl's Jeans (Grey) 48.75  -  -  -  - 
26 Girl's Hoodie (White) 39.91  -  -  -  - 
27 Boy's Coat (Blue) 10.24  -  -  -  - 
28 Men's Hoodie (Red) 24.71  -  -  -  - 
29 Boy's Shirt (Black) 37.34  -  -  -  - 
30 Women's Pyjamas (Grey) 28.59  -  -  -  - 
31 Women's Skirt (Green) 5.65  -  -  -  - 
32 Women's Jacket (Blue) 37  -  -  -  - 
33 Boy's Pyjamas (Grey) 23.32  -  -  -  - 
34 Women's Jeans (Red) 7.18  -  -  -  - 
35 Girl's Dress (Red) 49.12  -  -  -  - 
44 Women's Coat (Black) 31.68  -  -  -  - 
45 Men's Jeans (Grey) 27.64  -  -  -  - 
46 Girl's Trousers (Red) 39.16  -  -  -  - 

46 rows selected.

PRODUCT_ID	PRODUCT_NAME	UNIT_PRICE	IMAGE_MIME_TYPE	IMAGE_FILENAME	IMAGE_CHARSET	IMAGE_LAST_UPDATED
36	Women's Trousers (Blue)	29.51	-	-	-	-
37	Boy's Jeans (Blue)	22.98	-	-	-	-
38	Girl's Pyjamas (Red)	11	-	-	-	-
39	Boy's Trousers (Blue)	34.06	-	-	-	-
40	Girl's Pyjamas (Black)	8.66	-	-	-	-
41	Women's Dress (Black)	10.11	-	-	-	-
42	Boy's Jeans (Black)	16.64	-	-	-	-
43	Boy's Trousers (Black)	39.32	-	-	-	-
1	Boy's Shirt (White)	29.55	-	-	-	-
2	Women's Shirt (Green)	16.67	-	-	-	-
3	Boy's Sweater (Green)	44.17	-	-	-	-
4	Boy's Trousers (White)	43.71	-	-	-	-
5	Girl's Shorts (Red)	38.28	-	-	-	-
6	Boy's Socks (Grey)	19.16	-	-	-	-
7	Boy's Socks (Black)	19.58	-	-	-	-
8	Boy's Coat (Brown)	21.16	-	-	-	-
9	Women's Jeans (Brown)	29.49	-	-	-	-
10	Women's Skirt (Red)	30.69	-	-	-	-
11	Boy's Shorts (Blue)	10.48	-	-	-	-
12	Boy's Socks (White)	12.64	-	-	-	-
13	Boy's Hoodie (Grey)	26.14	-	-	-	-
14	Women's Skirt (Brown)	13.97	-	-	-	-
15	Girl's Coat (Blue)	13.09	-	-	-	-
16	Women's Socks (Grey)	39.89	-	-	-	-
17	Women's Sweater (Brown)	24.46	-	-	-	-
18	Women's Jacket (Black)	14.34	-	-	-	-
19	Men's Coat (Red)	28.21	-	-	-	-
20	Girl's Shorts (Green)	38.34	-	-	-	-
21	Girl's Pyjamas (White)	39.78	-	-	-	-
22	Men's Shorts (Black)	10.33	-	-	-	-
23	Men's Pyjamas (Blue)	48.39	-	-	-	-
24	Boy's Sweater (Red)	9.8	-	-	-	-
25	Girl's Jeans (Grey)	48.75	-	-	-	-
26	Girl's Hoodie (White)	39.91	-	-	-	-
27	Boy's Coat (Blue)	10.24	-	-	-	-
28	Men's Hoodie (Red)	24.71	-	-	-	-
29	Boy's Shirt (Black)	37.34	-	-	-	-
30	Women's Pyjamas (Grey)	28.59	-	-	-	-
31	Women's Skirt (Green)	5.65	-	-	-	-
32	Women's Jacket (Blue)	37	-	-	-	-
33	Boy's Pyjamas (Grey)	23.32	-	-	-	-
34	Women's Jeans (Red)	7.18	-	-	-	-
35	Girl's Dress (Red)	49.12	-	-	-	-
44	Women's Coat (Black)	31.68	-	-	-	-
45	Men's Jeans (Grey)	27.64	-	-	-	-
46	Girl's Trousers (Red)	39.16	-	-	-	-

Statement 19

From 19c you can also implement SELECT * EXCEPT as a table SQL macro.

SELECT * EXCEPT SQL 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 20

select *  
from   except_cols_macro ( hr.employees, columns ( employee_id ) )


FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000  -  - 90
Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000  - 100 90
Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000  - 100 90
Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000  - 102 60
Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000  - 103 60
David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800  - 103 60
Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800  - 103 60
Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200  - 103 60
Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008  - 101 100
Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000  - 108 100
John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200  - 108 100
Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700  - 108 100
Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800  - 108 100
Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900  - 108 100
Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100 30
Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114 30
Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114 30
Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114 30
Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114 30
Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500  - 114 30
Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000  - 100 50
Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200  - 100 50
Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900  - 100 50
Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500  - 100 50
Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800  - 100 50
Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200  - 120 50
Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700  - 120 50
James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400  - 120 50
Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200  - 120 50
Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300  - 121 50
Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800  - 121 50
James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500  - 121 50
TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100  - 121 50
Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300  - 122 50
Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900  - 122 50
Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400  - 122 50
Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200  - 122 50
Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600  - 123 50
Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200  - 123 50
John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700  - 123 50
Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500  - 123 50
Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500  - 124 50
Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100  - 124 50
Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600  - 124 50
Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500  - 124 50
John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80
Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80
Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80
Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80

Rows 1 - 50. More rows exist.

FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24000	-	-	90
Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60
Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60
Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	12008	-	101	100
Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100
Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT	6900	-	108	100
Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100	30
Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114	30
Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114	30
Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114	30
Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114	30
Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK	2500	-	114	30
Matthew	Weiss	MWEISS	650.123.1234	18-JUL-04	ST_MAN	8000	-	100	50
Adam	Fripp	AFRIPP	650.123.2234	10-APR-05	ST_MAN	8200	-	100	50
Payam	Kaufling	PKAUFLIN	650.123.3234	01-MAY-03	ST_MAN	7900	-	100	50
Shanta	Vollman	SVOLLMAN	650.123.4234	10-OCT-05	ST_MAN	6500	-	100	50
Kevin	Mourgos	KMOURGOS	650.123.5234	16-NOV-07	ST_MAN	5800	-	100	50
Julia	Nayer	JNAYER	650.124.1214	16-JUL-05	ST_CLERK	3200	-	120	50
Irene	Mikkilineni	IMIKKILI	650.124.1224	28-SEP-06	ST_CLERK	2700	-	120	50
James	Landry	JLANDRY	650.124.1334	14-JAN-07	ST_CLERK	2400	-	120	50
Steven	Markle	SMARKLE	650.124.1434	08-MAR-08	ST_CLERK	2200	-	120	50
Laura	Bissot	LBISSOT	650.124.5234	20-AUG-05	ST_CLERK	3300	-	121	50
Mozhe	Atkinson	MATKINSO	650.124.6234	30-OCT-05	ST_CLERK	2800	-	121	50
James	Marlow	JAMRLOW	650.124.7234	16-FEB-05	ST_CLERK	2500	-	121	50
TJ	Olson	TJOLSON	650.124.8234	10-APR-07	ST_CLERK	2100	-	121	50
Jason	Mallin	JMALLIN	650.127.1934	14-JUN-04	ST_CLERK	3300	-	122	50
Michael	Rogers	MROGERS	650.127.1834	26-AUG-06	ST_CLERK	2900	-	122	50
Ki	Gee	KGEE	650.127.1734	12-DEC-07	ST_CLERK	2400	-	122	50
Hazel	Philtanker	HPHILTAN	650.127.1634	06-FEB-08	ST_CLERK	2200	-	122	50
Renske	Ladwig	RLADWIG	650.121.1234	14-JUL-03	ST_CLERK	3600	-	123	50
Stephen	Stiles	SSTILES	650.121.2034	26-OCT-05	ST_CLERK	3200	-	123	50
John	Seo	JSEO	650.121.2019	12-FEB-06	ST_CLERK	2700	-	123	50
Joshua	Patel	JPATEL	650.121.1834	06-APR-06	ST_CLERK	2500	-	123	50
Trenna	Rajs	TRAJS	650.121.8009	17-OCT-03	ST_CLERK	3500	-	124	50
Curtis	Davies	CDAVIES	650.121.2994	29-JAN-05	ST_CLERK	3100	-	124	50
Randall	Matos	RMATOS	650.121.2874	15-MAR-06	ST_CLERK	2600	-	124	50
Peter	Vargas	PVARGAS	650.121.2004	09-JUL-06	ST_CLERK	2500	-	124	50
John	Russell	JRUSSEL	011.44.1344.429268	01-OCT-04	SA_MAN	14000	.4	100	80
Karen	Partners	KPARTNER	011.44.1344.467268	05-JAN-05	SA_MAN	13500	.3	100	80
Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	10-MAR-05	SA_MAN	12000	.3	100	80
Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	15-OCT-07	SA_MAN	11000	.3	100	80
Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	29-JAN-08	SA_MAN	10500	.2	100	80

Statement 21

Using table macros in the WITH clause is unsupported

WITH clause - table SQL macros

with rws as ( 
  select * 
  from   except_cols_macro ( hr.employees, columns ( employee_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 22

You can use PTFs in the WITH clause

WITH clause - PTFs

with rws as ( 
  select * 
  from   except_cols_pkg.except_cols ( hr.employees, columns ( employee_id ) ) 
) 
  select * from rws


FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000  -  - 90
Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000  - 100 90
Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000  - 100 90
Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000  - 102 60
Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000  - 103 60
David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800  - 103 60
Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800  - 103 60
Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200  - 103 60
Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008  - 101 100
Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000  - 108 100
John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200  - 108 100
Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700  - 108 100
Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800  - 108 100
Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900  - 108 100
Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000  - 100 30
Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100  - 114 30
Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900  - 114 30
Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800  - 114 30
Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600  - 114 30
Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500  - 114 30
Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000  - 100 50
Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200  - 100 50
Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900  - 100 50
Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500  - 100 50
Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800  - 100 50
Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200  - 120 50
Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700  - 120 50
James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400  - 120 50
Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200  - 120 50
Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300  - 121 50
Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800  - 121 50
James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500  - 121 50
TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100  - 121 50
Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300  - 122 50
Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900  - 122 50
Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400  - 122 50
Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200  - 122 50
Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600  - 123 50
Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200  - 123 50
John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700  - 123 50
Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500  - 123 50
Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500  - 124 50
Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100  - 124 50
Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600  - 124 50
Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500  - 124 50
John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80
Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80
Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80
Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80

Rows 1 - 50. More rows exist.

FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
Steven	King	SKING	515.123.4567	17-JUN-03	AD_PRES	24000	-	-	90
Neena	Kochhar	NKOCHHAR	515.123.4568	21-SEP-05	AD_VP	17000	-	100	90
Lex	De Haan	LDEHAAN	515.123.4569	13-JAN-01	AD_VP	17000	-	100	90
Alexander	Hunold	AHUNOLD	590.423.4567	03-JAN-06	IT_PROG	9000	-	102	60
Bruce	Ernst	BERNST	590.423.4568	21-MAY-07	IT_PROG	6000	-	103	60
David	Austin	DAUSTIN	590.423.4569	25-JUN-05	IT_PROG	4800	-	103	60
Valli	Pataballa	VPATABAL	590.423.4560	05-FEB-06	IT_PROG	4800	-	103	60
Diana	Lorentz	DLORENTZ	590.423.5567	07-FEB-07	IT_PROG	4200	-	103	60
Nancy	Greenberg	NGREENBE	515.124.4569	17-AUG-02	FI_MGR	12008	-	101	100
Daniel	Faviet	DFAVIET	515.124.4169	16-AUG-02	FI_ACCOUNT	9000	-	108	100
John	Chen	JCHEN	515.124.4269	28-SEP-05	FI_ACCOUNT	8200	-	108	100
Ismael	Sciarra	ISCIARRA	515.124.4369	30-SEP-05	FI_ACCOUNT	7700	-	108	100
Jose Manuel	Urman	JMURMAN	515.124.4469	07-MAR-06	FI_ACCOUNT	7800	-	108	100
Luis	Popp	LPOPP	515.124.4567	07-DEC-07	FI_ACCOUNT	6900	-	108	100
Den	Raphaely	DRAPHEAL	515.127.4561	07-DEC-02	PU_MAN	11000	-	100	30
Alexander	Khoo	AKHOO	515.127.4562	18-MAY-03	PU_CLERK	3100	-	114	30
Shelli	Baida	SBAIDA	515.127.4563	24-DEC-05	PU_CLERK	2900	-	114	30
Sigal	Tobias	STOBIAS	515.127.4564	24-JUL-05	PU_CLERK	2800	-	114	30
Guy	Himuro	GHIMURO	515.127.4565	15-NOV-06	PU_CLERK	2600	-	114	30
Karen	Colmenares	KCOLMENA	515.127.4566	10-AUG-07	PU_CLERK	2500	-	114	30
Matthew	Weiss	MWEISS	650.123.1234	18-JUL-04	ST_MAN	8000	-	100	50
Adam	Fripp	AFRIPP	650.123.2234	10-APR-05	ST_MAN	8200	-	100	50
Payam	Kaufling	PKAUFLIN	650.123.3234	01-MAY-03	ST_MAN	7900	-	100	50
Shanta	Vollman	SVOLLMAN	650.123.4234	10-OCT-05	ST_MAN	6500	-	100	50
Kevin	Mourgos	KMOURGOS	650.123.5234	16-NOV-07	ST_MAN	5800	-	100	50
Julia	Nayer	JNAYER	650.124.1214	16-JUL-05	ST_CLERK	3200	-	120	50
Irene	Mikkilineni	IMIKKILI	650.124.1224	28-SEP-06	ST_CLERK	2700	-	120	50
James	Landry	JLANDRY	650.124.1334	14-JAN-07	ST_CLERK	2400	-	120	50
Steven	Markle	SMARKLE	650.124.1434	08-MAR-08	ST_CLERK	2200	-	120	50
Laura	Bissot	LBISSOT	650.124.5234	20-AUG-05	ST_CLERK	3300	-	121	50
Mozhe	Atkinson	MATKINSO	650.124.6234	30-OCT-05	ST_CLERK	2800	-	121	50
James	Marlow	JAMRLOW	650.124.7234	16-FEB-05	ST_CLERK	2500	-	121	50
TJ	Olson	TJOLSON	650.124.8234	10-APR-07	ST_CLERK	2100	-	121	50
Jason	Mallin	JMALLIN	650.127.1934	14-JUN-04	ST_CLERK	3300	-	122	50
Michael	Rogers	MROGERS	650.127.1834	26-AUG-06	ST_CLERK	2900	-	122	50
Ki	Gee	KGEE	650.127.1734	12-DEC-07	ST_CLERK	2400	-	122	50
Hazel	Philtanker	HPHILTAN	650.127.1634	06-FEB-08	ST_CLERK	2200	-	122	50
Renske	Ladwig	RLADWIG	650.121.1234	14-JUL-03	ST_CLERK	3600	-	123	50
Stephen	Stiles	SSTILES	650.121.2034	26-OCT-05	ST_CLERK	3200	-	123	50
John	Seo	JSEO	650.121.2019	12-FEB-06	ST_CLERK	2700	-	123	50
Joshua	Patel	JPATEL	650.121.1834	06-APR-06	ST_CLERK	2500	-	123	50
Trenna	Rajs	TRAJS	650.121.8009	17-OCT-03	ST_CLERK	3500	-	124	50
Curtis	Davies	CDAVIES	650.121.2994	29-JAN-05	ST_CLERK	3100	-	124	50
Randall	Matos	RMATOS	650.121.2874	15-MAR-06	ST_CLERK	2600	-	124	50
Peter	Vargas	PVARGAS	650.121.2004	09-JUL-06	ST_CLERK	2500	-	124	50
John	Russell	JRUSSEL	011.44.1344.429268	01-OCT-04	SA_MAN	14000	.4	100	80
Karen	Partners	KPARTNER	011.44.1344.467268	05-JAN-05	SA_MAN	13500	.3	100	80
Alberto	Errazuriz	AERRAZUR	011.44.1344.429278	10-MAR-05	SA_MAN	12000	.3	100	80
Gerald	Cambrault	GCAMBRAU	011.44.1344.619268	15-OCT-07	SA_MAN	11000	.3	100	80
Eleni	Zlotkey	EZLOTKEY	011.44.1344.429018	29-JAN-08	SA_MAN	10500	.2	100	80

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database