We help IT Professionals succeed at work.

Export (large) result in SQL Developer

ahoor
ahoor asked
on
14,768 Views
Last Modified: 2012-05-07
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).
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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...
Top Expert 2009

Commented:
sqlplus runs on your client PC as well.

Also, you should consider trying Toad, it is superior for this task.

Commented:
>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.  
CERTIFIED EXPERT
Top Expert 2008

Commented:
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.

Author

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.

Author

Commented:
Spool seems to work in sql developer except without the comma seperation that sqlplus uses and Excell recognises.

Author

Commented:
What I meant is in sql developer the commands like 'linesize pagesize colsep' don't apply.
CERTIFIED EXPERT
Top Expert 2008

Commented:
Comma separation is added artificially so:

SELECT column1 || ',' || column2 || ',' || column3 ...
FROM that_table;

Author

Commented:
The links in the answer give the solution. Not entirely saitsfactory since you still have to manually delete all headers every 50000 rows.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.