Solved

Export (large) result in SQL Developer

Posted on 2009-06-30
10
8,011 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 11

Accepted Solution

by:
Andytw earned 200 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
 
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now