Solved

Viewing all the output rows in SQLdeveloper

Posted on 2014-11-14
23
498 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 4
  • +2
23 Comments
 
LVL 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 77

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
 
LVL 77

Expert Comment

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

Expert Comment

by:Gerwin Jansen, EE MVE
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 77

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

724 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