?
Solved

Export (large) result in SQL Developer

Posted on 2009-06-30
10
Medium Priority
?
10,452 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Technology Partners: 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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

762 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