Re: formatting columns in SQL*Plus

On 22 Dec 2005 13:14:38 -0800, "Jeff Calico" <jeffCalico_at_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