Re: formatting columns in SQL*Plus

From: Jeff Calico <jeffCalico_at_hotmail.com>
Date: 23 Dec 2005 07:34:17 -0800
Message-ID: <1135352057.257864.76050@g49g2000cwa.googlegroups.com>

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.

• CODE---------------------------------- set heading off set pagesize 0 set echo OFF set feedback OFF spool jeff2.sql

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; ',
                          '' )

FROM Cols
WHERE table_name = UPPER('&1');

spool off

set pagesize 80
set heading ON

@jeff2

SELECT * FROM &1;

---

Thanks,
Jeff Received on Fri Dec 23 2005 - 09:34:17 CST