1
votes

the following query works perfectly fine when used in Oracle SQL Developer:

SET ECHO OFF
SET FEEDBACK OFF
SET LINES 1000
SET LINESIZE 150

COLUMN URL FORMAT a54

SELECT DISTINCT RPAD (ROUTE.URL, 54) URL
FROM ROUTE
ORDER BY URL ASC;

But when issued in my SQLPlus, for some reason, the result rows are divided into 11-lines blocks:

URL
-------
url-1
url-2
url-3
...
url-11

URL
-------
url-12
url-13
url-14
...
url-22

etc..

Why does it happening? and how can I get the results in one block?

I use SQLPlus 11.2.0.3.0

1

1 Answers

2
votes

You're already using set linesize - twice in fact, you set that to 1000, then immediately reduce to 150:

SET LINES 1000
SET LINESIZE 150

I suspect you might have expected the first setting to be the number of lines, rather than the length of each line, but they are the same setting - one is just a shortened version.

To set the number of lines in each block, or page, use the slightly-related set pagesize instead, e.g.:

SET PAGESIZE 1000
SET LINESIZE 150

The default value is 14, which means you get 11 lines of data, plus the header row, the row under that with dashes, and a blank line between pages.

If you set pagesize to zero then all results will be in a single 'page', however many lines of output you have; but that also suppresses the column headings.

Read more about setting page dimensions.