Export (large) result in SQL Developer

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

Database: Oracle 10.2.0.4
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:
right-click
export data
xls
<type filename>
apply

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).
LVL 3
ahoorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndytwCommented:
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
http://www.experts-exchange.com/Programming/Languages/Q_24463878.html

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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...
0
mrjoltcolaCommented:
sqlplus runs on your client PC as well.

Also, you should consider trying Toad, it is superior for this task.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AndytwCommented:
>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.  
0
schwertnerCommented:
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

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


spool off


It works. Use it.
0
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.
0
ahoorAuthor Commented:
Spool seems to work in sql developer except without the comma seperation that sqlplus uses and Excell recognises.
0
ahoorAuthor Commented:
What I meant is in sql developer the commands like 'linesize pagesize colsep' don't apply.
0
schwertnerCommented:
Comma separation is added artificially so:

SELECT column1 || ',' || column2 || ',' || column3 ...
FROM that_table;
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.