Avatar of Newbie345
Newbie345
 asked on

Viewing all the output rows in SQLdeveloper

Hi,
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?
Oracle Database

Avatar of undefined
Last Comment
Gerwin Jansen

8/22/2022 - Mon
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.
ASKER CERTIFIED SOLUTION
yawkey13

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
yawkey13

You can also set the Sql Array Fetch Size in Database > Advanced.
Newbie345

ASKER
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gerwin Jansen

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.
Newbie345

ASKER
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

You've put in your own query right?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Newbie345

ASKER
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

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)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Newbie345

ASKER
I was having some issues with using sqlplus. I will give it a try when I get a chance.

Gerwin,
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)

Verify you have 'spool off' at the end of the script.
Gerwin Jansen

>> 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 ;)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Helena Marková

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

@henka - The export option is not what asker wants, see above.
Helena Marková

Oh, sorry :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Newbie345

ASKER
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

How large is the file when it reaches 37000 lines? On what filesystem is the file and do you get any error?
Newbie345

ASKER
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 2.1.1.64 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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gerwin Jansen

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)

Newbie345,

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

It doesn't appear to answer the question.
Newbie345

ASKER
Hi,
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gerwin Jansen

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.