Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: formatting columns in SQL*Plus
Sybrand Bakker wrote:
> On 22 Dec 2005 13:14:38 -0800, "Jeff Calico" <jeffCalico_at_hotmail.com>
> wrote:
>
> just spool the format commands to a file, prior to spooling the sql.
> Alternatively, it is possible to spool output in html, the output will
> end up in html tables, and is scrollable in your browser.
>
> --
> Sybrand Bakker, Senior Oracle DBA
Thanks for the reply. Here is what I have come up with so far, which
seems to work ok,
except I can't get it to suppress printing substitution messages:
--------OUTPUT (CODE FOLLOWS BELOW)--------
old 5: WHERE table_name = '&1'
new 5: WHERE table_name = 'My_Table'
column ASCII_NME format a20 truncate;
column CRT_DATE format a20 truncate;
column ACCSS_DATE format a20 truncate;
column MDFY_DATE format a20 truncate;
column ATTRIBUTES format a20 truncate;
SP2-0734: unknown command beginning "old 5: W..." - rest of
line ignored.
SP2-0734: unknown command beginning "new 5: W..." - rest of
line ignored.
select 'set echo off' FROM Dual;
select 'set feedback off' FROM Dual;
SELECT DECODE( data_type, 'VARCHAR2', 'column '|| column_name || ' format a20 truncate; ',
'TIMESTAMP(6) WITH TIME ZONE', 'column '|| column_name || ' format a20 truncate; ', '' )
spool off
set pagesize 80
set heading ON
@jeff2
SELECT * FROM &1;