I’m building your standard Select statement, but I hate looking at null fields so I’m tossing those out. It does take a little while to generate depending on the size of the table and how many null fields there are. Like our client's table has about 20 null fields and 3.5M records and it took 3 minutes to write my query. I spool a generated SQL query to a .sql file and then execute it.
DEFINE schema = fss_prd DEFINE tbl = client_table -- Press F5 -- SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET TIMING OFF SPOOL h:\test.sql SELECT 'SELECT CASE WHEN ' || TO_CHAR(column_id) || '= 1 THEN ''SELECT '' ELSE '' , '' END ||LOWER(''' || column_name || ''') FROM &schema..&tbl WHERE ROWNUM < 2 AND ' || column_name || ' IS NOT NULL UNION ALL' FROM all_tab_columns WHERE owner = UPPER('&schema') AND table_name = UPPER('&tbl') ORDER BY column_id ; SELECT 'SELECT '' FROM &schema..&tbl;'' FROM DUAL;' FROM DUAL; SPOOL OFF @h:\test