Viewing all the output rows in SQLdeveloper

I am running an Oracle SQL query that returns potentially thousands of rows. Right now, it is only displaying 50 rows initially and as I scroll down it loads 50 more rows and so on. Is there a way so all the rows are loaded at once?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
not a sql developer user but I believe the worksheet view handles most of the sqlplus options.

In sqlplus:
--remove extra stuff
set pages 0
set lines 1000
set trimspool on
set feedback off
spool myfile.txt
select ...;
spool off

Open in new window

Set the lines size (lines) to a number that will account for ALL the data on a single line.

If you cannot get SQL Developer to do this, use sqlplus.  I find sqlplus easier/quicker anyway.
With focus on the results, press ctrl-pgdown and it will load all results.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You can also set the Sql Array Fetch Size in Database > Advanced.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Newbie345Author Commented:
Thanks. What would be an ideal fetch size? When I change the rows in Sql Array Fetch Size in Database > Advanced to a million and I rerun the query, I am getting weird errors like java heap space, OALL8 is in inconsistent state, Bigger type length than maximum etc. They don't appear when the fetch size is 50
Gerwin Jansen, EE MVETopic Advisor Commented:
If you're getting into a high amount of rows, I'd export to csv if I were you. Right click the results and export, choose output type etc.

'Seeing' all the rows is not what you want probably, you would want to search for something or sort maybe. That's a lot easier in a csv file, imported into Excel for example.
Newbie345Author Commented:
Hi Gerwin,
There was another question from me related to the same issue. Actually, I am not interested in seeing all the rows within the SQL developer. I would like all the output to save to a file automatically with a single query execute (thus avoiding the manual steps of right click and export). You were suggesting to use spool for that.

I ran the query below and it created the file in the specified folder. But the file is zero length. There are no errors when I ran it. Any ideas why the file is zero length?

spool c:\temp\output.csv
select col1
|| '|' || col2
|| '|' || col3
from table;
spool off
Gerwin Jansen, EE MVETopic Advisor Commented:
You've put in your own query right?
Newbie345Author Commented:
Yes, I ran the query as is (please see last post) from the sql developer. It starts with spool, then select statement and ends with 'spool off'. I am not sure what you meant. Should I run the query in any other manner?
Gerwin Jansen, EE MVETopic Advisor Commented:
Just put the above in your sql worksheet and run it using F5 (or run as script).

A basic (working) example is this:

spool c:\temp\test.csv
select /*csv*/ * from cat;
spool off;

The only minor issue is that sql developer puts the query itself in the output file as well (line #1), suggest you delete that or start importing in Excel (or other application), starting from line #2 which has the header.
(sql developer does not honor a 'set termout off' which would hide the sql command from the output, as it does in sqlplus)

I know you want to export your output just by pressing a button (or loading a script) but sql developer is called developer for a reason, if you want batch created output, sqlplus is much easier.
slightwv (䄆 Netminder) Commented:
Agreed and I posted sqlplus above:  GUI's are good from some things when you don't know the command line but at times it is hard to beat the command line.

Do not fear the command line.

Use sqlplus.
Newbie345Author Commented:
I was having some issues with using sqlplus. I will give it a try when I get a chance.

I am still seeing the zero length file issue in SQL developer. The file is getting created promptly but it's always zero length. The select query should return only handful of rows so data volume is not an issue. For some reason, it's creating the file but not writing to it.
slightwv (䄆 Netminder) Commented:
Verify you have 'spool off' at the end of the script.
Gerwin Jansen, EE MVETopic Advisor Commented:
>> I am still seeing the zero length file issue in SQL developer.
It is running fine on my side.

>> I am still seeing the zero length file issue in SQL developer.
Which version of sql developer are you using and which OS? Did you copy the exact example I gave you or some other query? I suspect another query, if you have another query, try it first, then add the hint directly after select:

select /*csv*/ your query remainder

For next questions: If  you're having issues with sqlplus and are trying the same in sql developer, it's best to put that information in the question. As you see above, you are getting replies on the sqlplus solution as well ;)
Helena Markováprogrammer-analystCommented:
I have tried above code on Oracle SQL Developer 1.5.5 on Windows 7 and csv file is empty, too.
But you can use Export Data (right click on view or table) and choose xls and xls file contains formatted data. You can try also csv but data are not well formatted.
Gerwin Jansen, EE MVETopic Advisor Commented:
@henka - The export option is not what asker wants, see above.
Helena Markováprogrammer-analystCommented:
Oh, sorry :)
Newbie345Author Commented:
Sorry for the delay. SQL Plus seems to be working fine. I am able to divert the output to a text file. However, it seems to have a row count limitation of around 37,000? Is it possible to write more rows (I was thinking of about million rows) from SQL Plus?

Also I don't want to see the output in the SQL Plus (just the flat file). Is it possible to suppress the output within SQL Plus and just let it write to the text file. I did use 'set feedback off' but it still writes the output directly in SQL Plus
Gerwin Jansen, EE MVETopic Advisor Commented:
How large is the file when it reaches 37000 lines? On what filesystem is the file and do you get any error?
Newbie345Author Commented:
I was trying to set the line count by running the following in SQLPlus. So I didn't even start running the SQL select statement by that time

set lines 1000000
I get the error like
SP2-0267: linesize option 1000000 out of range <1 through 32767>

Oracle database is 11g Enterprise
I am running Oracle SQL Developer on Windows XP Professional server

Also I don't want to see the output in the SQL Plus (just the flat file). Is it possible to suppress the output within SQL Plus and just let it write to the text file. I did use set feedback off but it still writes the output directly in SQL Plus
Gerwin Jansen, EE MVETopic Advisor Commented:
set lines is short for set linesize - the width of the output, no relation to amount of lines in your output.

How large is your #37000 lines file?
slightwv (䄆 Netminder) Commented:

Can I ask why you selected the post you did as the answer?

It doesn't appear to answer the question.
Newbie345Author Commented:
None of the solutions seemed to address my question. At the same time, I didn't want to abandon the question. So just wanted to get it done by picking the one that atleast works.
Gerwin Jansen, EE MVETopic Advisor Commented:
Maybe your question was not clear. Viewing all output lines will only work when you get about 20-30 lines of output. The solution you've now chosen just loads all but displays no more than fit on screen.  Deleting this question is also an option.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.