Solved

Viewing all the output rows in SQLdeveloper

Posted on 2014-11-14
23
347 Views
Last Modified: 2015-01-22
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?
0
Comment
Question by:Newbie345
  • 8
  • 7
  • 4
  • +2
23 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40444024
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.
0
 
LVL 6

Accepted Solution

by:
yawkey13 earned 500 total points
ID: 40444032
With focus on the results, press ctrl-pgdown and it will load all results.
0
 
LVL 6

Expert Comment

by:yawkey13
ID: 40444033
You can also set the Sql Array Fetch Size in Database > Advanced.
0
 

Author Comment

by:Newbie345
ID: 40444048
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
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40444208
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.
0
 

Author Comment

by:Newbie345
ID: 40444579
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
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40444793
You've put in your own query right?
0
 

Author Comment

by:Newbie345
ID: 40444796
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?
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40444923
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40445031
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.
0
 

Author Comment

by:Newbie345
ID: 40445202
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40446213
Verify you have 'spool off' at the end of the script.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40446666
>> 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 ;)
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40451821
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.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40451870
@henka - The export option is not what asker wants, see above.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 40451967
Oh, sorry :)
0
 

Author Comment

by:Newbie345
ID: 40458887
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
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40459794
How large is the file when it reaches 37000 lines? On what filesystem is the file and do you get any error?
0
 

Author Comment

by:Newbie345
ID: 40459831
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
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40460079
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40564295
Newbie345,

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

It doesn't appear to answer the question.
0
 

Author Comment

by:Newbie345
ID: 40564301
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.
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40564709
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now