--
-- Author: Mohit Dubey (Based on Thomas Kyte's idea http://asktom.oracle.com)
-- Visit http://www.geocities.com/md_seraphin for more goodies!
--
-- This program is distributed under the GNU Public License Version 2
-- with the additional privisio that the original author's name and
-- contact details must be retained as-is in any modified or copied
-- versions of this program.
--

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
set feedback off verify off echo off
accept tab prompt "Enter table name : "
prompt To enter strings in the WHERE clause or ORDER BY, enclose it 
prompt within double single quotes instead of the usual single quote
accept wher      prompt "Enter Where clause {default is none} : "
accept sortorder prompt "Enter Order by clause <1,3,5,..> {default is unordered } : "
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 
   FROM dual;

spool vertdisp.sql

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

prompt SELECT

SELECT
    COLUMN_NAME,
    'RPAD('||''''||COLUMN_NAME||' = '||
        ''''||',22,'||''''||'-'||''''||') '||'||'||
    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
    USER_TAB_COLUMNS
WHERE
    TABLE_NAME = UPPER('&tab') AND
    DATA_TYPE NOT LIKE ('%RAW') AND
    COLUMN_NAME < (SELECT MAX(COLUMN_NAME)
                 FROM USER_TAB_COLUMNS
                 WHERE 
                       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 &tab '||' &wherclause '||' &orderby ;' cl
FROM
    USER_TAB_COLUMNS
WHERE
    TABLE_NAME = UPPER('&tab') AND
    DATA_TYPE NOT LIKE ('%RAW') AND
    COLUMN_NAME  = (SELECT MAX(COLUMN_NAME )
                 FROM USER_TAB_COLUMNS
                 WHERE
                       TABLE_NAME = UPPER('&tab'))
ORDER BY COLUMN_NAME;
spool off
start vertdisp
clear colu
host rm vertdisp.sql