CODEWITHSUNDEEP
sqloracle-databasesqlpluspage-break
SandPiper
SandPiper

Reputation: 2906

How to spool separate queries to same file without a page break?

I am trying to run multiple separate queries in one script using SQL*Plus and spool the output to a file. It is currently spooling out, but it is inserting a page break between all queries, and I only want a page break between specific ones.

What I am doing:

SPOOL directory/QUERY_OUTPUT_FILE

SELECT .... FROM QUERY1
/
-- No page break desired here, but getting one anyway

SELECT .... FROM QUERY2
/
-- Page break desired here

SELECT .... FROM QUERY3
/

SPOOL OFF

How can I control whether a page break is inserted or not between these queries?

Upvotes: 1

Views: 3429

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Presumably you're currently setting newpage to zero, either in your script or in a local or global login script.

Sets the number of blank lines to be printed from the top of each page to the top title. A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals. If you set NEWPAGE to NONE, SQL*Plus does not print a blank line or formfeed between the report pages.

You can use that setting to control when you see a page break; set it to a non-zero value to have blank lines between the first two, then switch it back to zero to get line breaks back

For example:

SET pages 1000
SET feedback OFF

SPOOL spool_test

SET newpage NONE

SELECT * FROM DUAL
connect by level < 5
/

SET newpage 3

SELECT * FROM DUAL
connect by level < 5
/

SET newpage 0

SELECT * FROM DUAL
connect by level < 5
/

SPOOL OFF

The spool_test.lst file that created looks like this:

D
-
X
X
X
X



D
-
X
X
X
X
^LD
-
X
X
X
X

At the start of the file there is nothing before the query output starts, because of the initial NONE setting. Between the first two query outputs there are three blank lines, from the second call to set newpage. Between the second and third there is just a ^L, which is a page break (and form feed) character - ASCII 12.

Upvotes: 3

Related Questions