formatting columns in SQL*Plus

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 Received on Thu Dec 22 2005 - 15:14:38 CST