Hello everyone,

I frequently want to display query results in SQL*Plus,
but they come out poorly formatted because
the default length of the columns causes excessive wrapping on lines.

I can fix the problem to some extent by:
set linesize 130
set pagesize 80

and then I can *manually* issue a bunch of format comands like:

column <columnName> format a40


What I would like to be able to do is run a script that
would take a table name as its input and it would detect the
columns and datatypes of the table and issue the column
format commands.

I initially thought I could do this with PL/SQL as follows:

Define a cursor:
CURSOR myCur IS SELECT column_name, data_type
FROM Cols
WHERE table_name = UPPER( tableName );

and then loop through the result set, get the data type of each column,
and then kick off the appropriate column format command. Unfortunately,
it seems that one can't issue SQL*Plus commands from a PL/SQL script.

So... I can try to do all the processing from SQL*Plus, but then I
don't know how to capture the output from a SQL statement and
loop through it to issue the column format commands...

any ideas?

thanks,
Jeff

On 22 Dec 2005 13:14:38 -0800, "Jeff Calico" <jeffCalico@hotmail.com>
wrote:

>Hello everyone,
>
>I frequently want to display query results in SQL*Plus,
>but they come out poorly formatted because
>the default length of the columns causes excessive wrapping on lines.
>
>I can fix the problem to some extent by:
>set linesize 130
>set pagesize 80
>
>and then I can *manually* issue a bunch of format comands like:
>
>column <columnName> format a40
>
>
>What I would like to be able to do is run a script that
>would take a table name as its input and it would detect the
>columns and datatypes of the table and issue the column
>format commands.
>
>I initially thought I could do this with PL/SQL as follows:
>
>Define a cursor:
> CURSOR myCur IS SELECT column_name, data_type
> FROM Cols
> WHERE table_name = UPPER( tableName );
>
>and then loop through the result set, get the data type of each column,
>and then kick off the appropriate column format command. Unfortunately,
>it seems that one can't issue SQL*Plus commands from a PL/SQL script.
>
>So... I can try to do all the processing from SQL*Plus, but then I
>don't know how to capture the output from a SQL statement and
>loop through it to issue the column format commands...
>
>any ideas?
>
>thanks,
>Jeff

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

Sybrand Bakker wrote:
> On 22 Dec 2005 13:14:38 -0800, "Jeff Calico" <jeffCalico@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

> 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:

To suppress printing substitution messages you can try "set verify off"

Best regards

Maxim