Export (large) result in SQL Developer

I have a problem exporting query results to an Excell file.

Database: Oracle
SQL Developer 1.5.4 build 5940 with patch 59.59
MS Excell 2007

I do a query like 'select * from table;'

When I get the resultset I do:
export data
<type filename>

This works usually fine, but now I have a large resultset.
'select * from bigtable where datecol = to_date(date);'

The resultset has 37 columns.
For a given day I have  41146 rows. This works, I get an xls file of about 22MB.
For another day I have 47040 rows. This is a problem. I get an empty xls file.

So, my question... is there an option for the number of rows? Or filesize?
Has anyone experienced something like this?

I can't find any info on the subject on any site (yet).
Who is Participating?
AndytwConnect With a Mentor Commented:
This could perhaps be a bug with SQL developer.  I'd recommend scripting this, generating the CSV file using SQL*Plus.  It will be a lot faster - you can just write straight to file rather than having the retrieve the resultset on screen.

A similar question was raised recently.  see Milleniumaire's posted solution (http:#24547921):

create csv file

ahoorAuthor Commented:
Ok, the sqlplus workaround works. But it seems that only works on the server, have to try to get the output to my local pc, hope I am allowed to map it.

Preferably I do it using sql developer though, so the question remains...
sqlplus runs on your client PC as well.

Also, you should consider trying Toad, it is superior for this task.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

>But it seems that only works on the server
Can you expand on that, what problems are you encountering? It doesn't just work on the server, so .  SQL*Plus is a client - it's a tool which connects to the database.  So, provided you have SQL*Plus installed on your local PC - you will be able get output on your local pc.  

Since you are running sql developer, I asumme you have the Oracle installed (and SQL*Plus) on your local machine.  
SQLDeveloper runs without installation of Oracle clients.
JDBC is the answer

Now, the expectectation "click and get the result" are somewhat too early ...
I use the slogan KISS:
"Keep it Simple and Smigling"
Some  ANGRY Purists will say me that the original is not this, but as usually I do not care ...

What I do with SQL Developer - the good old SPOOL:

spool c:\sqldeveloper.lst

select * from v$version

Oracle Database 10g Enterprise Edition Release - Prod
PL/SQL Release - Production                          
CORE      Production                                        
TNS for Linux: Version - Production                  
NLSRTL Version - Production                          

spool off

It works. Use it.
ahoorAuthor Commented:
I don't have Oracle client on my pc, will install that tomorrow and then try to use sqlplus.

Same error occurs when I try to 'export data' from a view.
ahoorAuthor Commented:
Spool seems to work in sql developer except without the comma seperation that sqlplus uses and Excell recognises.
ahoorAuthor Commented:
What I meant is in sql developer the commands like 'linesize pagesize colsep' don't apply.
Comma separation is added artificially so:

SELECT column1 || ',' || column2 || ',' || column3 ...
FROM that_table;
ahoorAuthor Commented:
The links in the answer give the solution. Not entirely saitsfactory since you still have to manually delete all headers every 50000 rows.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.