-- #############################################################################################
--
-- %Purpose: Formatted SELECT * FROM 'table' Statement Results
--
-- SQL Script to display data FROM any table in a Vertical Layout
-- and only 1 row per viewable page
--
-- Sample output for the emp table output format is :
--
--  DEPT = --------------------------CRIME [5]
--  EMP_NO = ------------------------1 [1]
--  FIRST_NAME = --------------------JOHN [4]
--  JOIN_DATE = ---------------------02/01/1999 17:38:56 [9]
--  LAST_NAME = ---------------------DOE [3]
--  MIDDLE_INITIAL = ----------------P [1]
--
--  Press return To Continue
--
-- #############################################################################################

col eol newline
set head off pages 0 numf 9999999999.99
set lines 200 wrap on trimspool on
prompt NOTE : LONG/RAW/LOB COLUMNS WILL NOT BE DISPLAYED
prompt
set feedback off verify off echo off
accept tab prompt "Enter table name: [Example: emp] "
prompt
prompt To enter strings in the WHERE clause or ORDER BY, enclose it
prompt within '''' single quotes instead of the usual single quote
prompt [Example: ename = ''''JAMES'''']
prompt
accept wher      prompt "Enter Where clause {default is none}: "
accept oner      prompt "Enter owner {default is current user}: "
accept sortorder prompt "Enter Order by clause <1,3,5,..> {default is unordered}: "
prompt
prompt Press Return ...
prompt
set termout off
col COLUMN_NAME noprint
col wherstmt new_val wherclause
col ordby new_val orderby
col usr new_val objuser
SELECT
   DECODE(NVL(LENGTH('&sortorder'),0),0,'' ,' ORDER BY &sortorder') ordby ,
   DECODE(NVL(LENGTH('&wher'),0),0,'' ,' WHERE  &wher') wherstmt ,
   NVL(UPPER('&oner'),USER) usr FROM dual;

spool vertdisp.sql

SELECT
    'set pages '||TO_CHAR(count(*)+2) eol,
    'set head off pause on numf 999999999999.99 lines 80 ' eol,
    'set feedback off verify off echo off termout on trimspool on' eol
FROM
    DBA_TAB_COLUMNS
WHERE OWNER = '&objuser'  AND
    TABLE_NAME = UPPER('&tab') AND
    DATA_TYPE NOT LIKE ('%RAW');

prompt SELECT

SELECT
    COLUMN_NAME,
    'RPAD('||''''||COLUMN_NAME||' = '||
        ''''||',33,'||''''||'-'||''''||') '||'||'||
    DECODE(DATA_TYPE,'DATE','TO_CHAR('||COLUMN_NAME||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')',
        COLUMN_NAME) || '  '||
    DECODE(DATA_TYPE,'NUMBER',DECODE(SIGN(DATA_SCALE-1),-1,' ||',
        ' ||'),' '||' ||')|| ''''|| ' ['||''''||'||'||
        ' TO_CHAR(NVL(LENGTH('||COLUMN_NAME||'),0))'||
        '||'||''''||']'||''''||' eol,' cl
FROM
    DBA_TAB_COLUMNS
WHERE
    OWNER = '&objuser'  AND
    TABLE_NAME = UPPER('&tab') AND
    DATA_TYPE NOT LIKE ('%RAW') AND
    COLUMN_NAME < (SELECT MAX(COLUMN_NAME)
                 FROM DBA_TAB_COLUMNS
                 WHERE OWNER = '&objuser'  AND
                       TABLE_NAME = UPPER('&tab'))
UNION
SELECT
    COLUMN_NAME,
    'RPAD('||''''||COLUMN_NAME||' = '||
        ''''||',33,'||''''||'-'||''''||') '||'||'||
    DECODE(DATA_TYPE,'DATE','TO_CHAR('||COLUMN_NAME||','||''''||'MM/DD/YYYY HH24:MI:SS'||''''||')',
        COLUMN_NAME) || '  '||
    DECODE(DATA_TYPE,'NUMBER',DECODE(SIGN(DATA_SCALE-1),-1,' ||',
        ' ||'),' '||' ||')|| ''''|| ' ['||''''||'||'||
        ' TO_CHAR(NVL(LENGTH('||COLUMN_NAME||'),0))'||
        '||'||''''||']'||''''||' eol'||
    ' FROM &objuser..&tab '||' &wherclause '||' &orderby ;' cl
FROM
    DBA_TAB_COLUMNS
WHERE
    OWNER = '&objuser'  AND
    TABLE_NAME = UPPER('&tab') AND
    DATA_TYPE NOT LIKE ('%RAW') AND
    COLUMN_NAME  = (SELECT MAX(COLUMN_NAME )
                 FROM DBA_TAB_COLUMNS
                 WHERE OWNER = '&objuser'  AND
                       TABLE_NAME = UPPER('&tab'))
ORDER BY COLUMN_NAME;
spool off
start vertdisp
clear colu