How to Dynamically Generate SELECT Statement with all Table Columns Excluding a Given List

Zahar Hilkevich

Problem: Dynamically Generate SELECT Statement with all Table Columns Excluding a Given List

Level: Intermediate/Advanced

Let say, we need to dynamically generate a SELECT statement that lists all columns from emp table except for hiredate and comm.

SQL Solution:

SELECT 'SELECT ' || LOWER(LISTAGG(column_name,', ') WITHIN GROUP (ORDER BY column_id)) || ' FROM ' || table_name AS SQL
FROM user_tab_columns
WHERE table_name='EMP'
  AND column_name NOT IN ('COMM', 'HIREDATE')
GROUP BY table_name
/

--Result:
SQL
------------------------------------------------------
SELECT empno, ename, job, mgr, sal, deptno FROM EMP

You can also do it in a number of different ways in PL/SQL.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Leave a comment