Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export (large) result in SQL Developer

Posted on 2009-06-30
10
Medium Priority
?
11,033 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).
0
Comment
Question by:ahoor
[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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 11

Accepted Solution

by:
Andytw earned 600 total points
ID: 24744168
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
 
LVL 3

Author Comment

by:ahoor
ID: 24744976
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24745054
sqlplus runs on your client PC as well.

Also, you should consider trying Toad, it is superior for this task.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Expert Comment

by:Andytw
ID: 24745062
>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
 
LVL 48

Expert Comment

by:schwertner
ID: 24745658
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
 
LVL 3

Author Comment

by:ahoor
ID: 24745960
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
 
LVL 3

Author Comment

by:ahoor
ID: 24746073
Spool seems to work in sql developer except without the comma seperation that sqlplus uses and Excell recognises.
0
 
LVL 3

Author Comment

by:ahoor
ID: 24746091
What I meant is in sql developer the commands like 'linesize pagesize colsep' don't apply.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24746945
Comma separation is added artificially so:

SELECT column1 || ',' || column2 || ',' || column3 ...
FROM that_table;
0
 
LVL 3

Author Closing Comment

by:ahoor
ID: 31598288
The links in the answer give the solution. Not entirely saitsfactory since you still have to manually delete all headers every 50000 rows.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

609 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