formatting output

[princessinthetower]

I realized that sqlplus is not intended to handle elaborate formatting but I'd like to achieve the following if possible: a list of lookup table values with page break after each table.

It should look like this based on select statements like:

select table.field1, table.field2 from lookuptable;

Countries

code value
1001 Canada
1002 Mexico
1003 USA

[page break]

Cities

code value
1001 Boston
1002 New York
1003 Seattle

[page break]

I've tried spooling it but I get the SQL> prompt included in the spooled file and can't figure out how to insert a break that will be understood when I open the file in Word.

 

[carp]

The problem is that your table name does not appear anywhere in your query. SQLPLUS bases its formatting on the SELECT list in your query - not the data source itself.

So you will need to figure out a way to include the name of your table in your query. Since the query you supplied obviously isn't giving the output you show, I will assume that what you are really trying to do is show the contents of several lookup tables. As long as you want the same number of columns from each table and their datatypes are compatible, the following approach might work for you:

BREAK ON tablename PAGE
SELECT 'Countries' "tablename", code, value FROM countries
UNION ALL
SELECT 'Cities',code, value FROM cities
UNION ALL
SELECT 'Area Codes',code,value FROM area_codes
.
.
.
.
.
;

 

[princessinthetower]

I'm sorry to confuse you. I am getting the correct results from my query; I just used 'lookuptable' as a generic example. I will have 30 or more tables.

Instead of

select table.field1, table.field2 from lookuptable;

I should have said

select code, value from Countries;
select code, value from Cities;

The issue is not returning the correct values but being able to spool the resulting file with the appropriate page breaks and without the SQL> prompt. Also some of the tables will have a third or forth column so UNION won't work for them.

 

[carp]

No - you didn't confuse me. But given

select field1, field2 from lookuptable;

there's no way that I'm aware of to use SQL to get:

lookuptable

field1 field2
valf11 valf12
valf21 valf22
.
.
.

However, if you create a table that contains all of your lookup table names, you could probably achieve the desired effect using PL/SQL and dynamic SQL.