Link to home
Create AccountLog in
Avatar of Newbie345

asked on

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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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.
Avatar of yawkey13
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You can also set the Sql Array Fetch Size in Database > Advanced.
Avatar of Newbie345


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
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.
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
You've put in your own query right?
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?
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.
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.
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.
Verify you have 'spool off' at the end of the script.
>> 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 ;)
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.
@henka - The export option is not what asker wants, see above.
Oh, sorry :)
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
How large is the file when it reaches 37000 lines? On what filesystem is the file and do you get any error?
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
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?

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

It doesn't appear to answer the question.
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.
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.