Forum Stats

  • 3,840,060 Users
  • 2,262,562 Discussions
  • 7,901,142 Comments

Discussions

Bug while exporting to Excel

user5717693
user5717693 Member Posts: 3
edited Apr 27, 2016 12:25PM in SQL Developer

Hi,

I've came across a bug during exporting data to Excel.

Basic info:

Windows 7 64 bit,

SQL Developer Version 4.1.3.20 Build MAIN-20.78,

Java(TM) SE Runtime Environment (build 1.8.0_66-b18)

Java HotSpot(TM) 64-Bit Server VM (build 25.66-b18, mixed mode)

Description:

2 connections to DB (different connections names, same credentials).

2 SQL queries run simultaneously by Run as Statement (longer ones)

After partial results are returned (complete results are bigger ones, with over 100 000 rows) right click on grid and Export as Excel 2003 to 2 different files.

Task Progress shows 2 processes. Firstly Executing Query, then Exporting and count of rows exported.

Bug arises when results of one query is being exported, and then kicks in exporting for second query.

First one finishes immediately, second goes on till the end but resulting file for first query is damaged and for second has 0 length and cannot be removed utils closing results grid in SQL Developer.

Looks like when second export starts during ongoing export of first query first one is being killed and results from second query are written to file of first query. Second file just sits there and waits for data that never will came.

Has anyone noticed similar behavior?

Tagged:

Answers

  • Unknown
    edited Apr 22, 2016 1:24PM

    Likely a bug.

    But you are definitely pressing your luck with Windows trying to do two operations like that at once anyway.

    So if you REALLY care more about actually getting that work done don't try to export/save two files at once in Sql Dev.

  • Gary Graham-Oracle
    Gary Graham-Oracle Member Posts: 3,256 Bronze Crown
    edited Apr 22, 2016 4:32PM

    The primary concern when doing exports to Excel is virtual memory consumption.  Rather than export to an .xls format (the whole result set is kept in memory), you'll have less memory consumption exporting to an .xlsx or .csv format.

    In addition, since you are apparently running a query from a worksheet rather than filtering from within a table/view object browser, you have the option of opening the worksheets as unshared.  You can actually open multiple unshared worksheets on the same connection name, so no real need to create two connection names with the same credentials. So, if there is some bug here related to the individual worksheets having to share the connection with their navigator trees in the Connections view (and possibly some open objects browsers on those), perhaps using unshared worksheets would mitigate that.

    But I totally agree that running just one large export at a time within a GUI is probably safest.

    user5717693
  • user5717693
    user5717693 Member Posts: 3
    edited Apr 27, 2016 8:59AM
    rp0428 wrote:
    
    Likely a bug.
    
    But you are definitely pressing your luck with Windows trying to do two operations like that at once anyway.
    
    So if you REALLY care more about actually getting that work done don't try to export/save two files at once in Sql Dev.
    

    So you are saying that if I have a car that allows driving straight and taking turns I should stick in driving straight because making two move at once may not work?

    And seriously, you are right about getting work done. That's how I'm doing it now. Just wanted let devs know about problem. As a side note I don't believe it Windows problem.

  • user5717693
    user5717693 Member Posts: 3
    edited Apr 27, 2016 9:08AM
    Gary Graham-Oracle wrote:
    
    The primary concern when doing exports to Excel is virtual memory consumption.  Rather than export to an .xls format (the whole result set is kept in memory), you'll have less memory consumption exporting to an .xlsx or .csv format.
    

    I'm using export to Excel 2003+ so as you propose .xlsx format

    In addition, since you are apparently running a query from a worksheet rather than filtering from within a table/view object browser, you have the option of opening the worksheets as unshared.  You can actually open multiple unshared worksheets on the same connection name, so no real need to create two connection names with the same credentials. So, if there is some bug here related to the individual worksheets having to share the connection with their navigator trees in the Connections view (and possibly some open objects browsers on those), perhaps using unshared worksheets would mitigate that.
    

    Good idea. I'll try this if occasion happens. Thank you!

    But I totally agree that running just one large export at a time within a GUI is probably safest.
    

    Sure, but also time consuming. Another solution I've used was runing 2 copies od SQL Dev at the same time.

  • Unknown
    edited Apr 27, 2016 12:25PM
     So you are saying that if I have a car that allows driving straight and taking turns I should stick in driving straight because making two move at once may not work? 
    

    No - I am saying if you try to drive straight and take turns AT THE SAME TIME you can expect trouble.

    A MAJOR consideration for production work (i.e. if you really need to get work done) is reliability and recoverability.

    Large exports or data transfers are inherently problematic and usually involved MULTIPLE resources each of which can also be problematic: DB server and OS, network, client server and OS, gui tool (sql dev, sql loader, etc).

    A problem with ANY of those can require the complete reexecution of an ETL job. So trying to execute two such jobs at the same time multiplies the risk and the time to redo BOTH operations if a failure occurs.

    Minimizing the amount of data for each transfer can provide for recovery. So if the data can be exported in 'chunks' (e.g. one region at a time to separate files) then a failure doesn't require re-exporting previous chunks. That allows for restarting an export for the failed chunk and less time to complete the overall operation if a failure occurs.

    But, yes, IMHO the Windows OS versions in particular have been prone to sudden, unexpected 'hiccups' that can cause failure of large ETL jobs. Whether that was a contributing factor for your case I don't know.

    Only you can determine the risk vs. reward appropriate for what you are doing and avoid taking 'unnecessary' risks.  Schedule large downloads/exports during your lunch break or overnight when success is more important than the extra time.

This discussion has been closed.