This content has been marked as final. Show 4 replies
This is a known issue with export to xls formats. You'll have better performance with export to csv.
You can also try increasing the AddVMOption -Xmx640M setting in your ...\sqldeveloper\ide\bin\ide.conf file.
SQL Developer Team
Any word out there as to if/when Oracle might be able to get a fix out by chance?
Been quite some time now this defect has had me using multiple tools.
This functionality relies on Apache POI. SQL Developer switched from POI 2.5.1 to 3.7 as of our release 3.1. POI 3.9 became available early December 2012, but I have no idea if/when it might replace 3.7 in our tool.
The Apache POI change log indicates there were performance and memory improvements in the 3.8 and 3.9 releases. Maybe one or more of those ( search the log for mem or perf ) could help in your case:
Follow-up to this thread regarding use of .csv technique.
The .csv technique is very fast in download speed. There of course is another pit-fall - Excel, when opening a data element in a .csv or .xml file, will drop the leading zeros. I've examined the data before Excel open, find Oracle's output is correct, and in my opinion, the fault lies in Microsoft for corrupting the data's integrity from the original content extracted.
There are two ways I an aware to export from Oracle using SQLDeveloper, and import to Excel to resolve data that needs to retain leading zeros - for example Item Numbers and Postal Zip Codes.
One is to export to a .csv...then open Excel and import via a data source. One must then change the data type from GENERAL to TEXT for each respective column needing leading zeros to be retained. I do not prefer this method, but it works.
The other is to export data as a .csv, but in the sql, code the data needing the work-around as such: *,'="' || to_char(a.Segment1) || '"' as Item_Number*. This example is for an Item Number/Segment1 situation. When the .csv is opened, the column displays correctly, but the data is a formula...not a data character string. Therfore, simply click on the column, copy and paste special as a value, and the data integrity is resolved. (may not even need to cpy/paste special if vlookups not being performed). So, this techinique seems to be a little for user friendly and effecient.
As an end-user, two bads don't make a right...shame on Oracle and SQLDeveloper for providing a defective output tool; shame on Microsoft for corrupting data at Excel open.
Edited by: emss on Mar 19, 2013 8:24 AM
Edited by: emss on Mar 19, 2013 8:26 AM