4 Replies Latest reply: Nov 21, 2013 9:04 AM by CAZuke RSS

    IN-Clause export issues


      Hi guys


      SQL DEV:

      Windows 8.1 64bit

      JRE 7u45 32bit

      I have 10GB of memory.


      I realise lately that whenever SQL developer fails to export into excel or any other format for that matter, it always happens when I use an IN-CLAUSE.

      Today it happened again where I can only have 151 items in my in clause. This is regardless of how many records are returned. If I add another value in the in-clause in the sql statement, the data wont export.

      The resulting excel file that is created is either broken (.xlsx) or empty (.xls). I know when it's going to fail with the export when I confirm the export parameters and in the status bar, I dont see the record count flashing. It just immediately returns control to the SQL developer window


      Is anyone familiar with this issue? I'm pretty sure it's not memory as I've exported MUCH larger files (without IN CLAUSE) before. The current one has 2 varchar columsn for 152 rows, and it only successfully exports 151 if I only put 151 fields in the in-clause.

      I'm not sure if it matters, but the inclause has varchars in (all are 10 characters).


      I cannot modify my query not to use IN.


      Is there any testing I can do to clarify the issue or change some setting to overcome this limit?

        • 1. Re: IN-Clause export issues
          Jeff Smith Sqldev Pm-Oracle

          Can you give us a repeatable example with a common dataset like HR.EMPLOYEES?


          Are you saying you're doing something like


          SELECT *
          WHERE EMPLOYEE_ID IN (1, 2, 3, ..., 152);



          • 2. Re: IN-Clause export issues



            Yes, it's sometimes really as simple as:

            select *

            from hr.employees

            where employee_id in (1,2,3,...152);


            BTW, I just tested a different query on another database and there it successfully exported beyond 152 expressions.

            I disconnected to the original database with the issue and still the limit seems to be in the same amount of expressions. changing select * to select column1 (and not other columns) doesn't seem to have an effect on the results.


            I'm thinking the issue may be in 3 different places:

            1. Memory of my client where the query is parsed (does this even happen in SQL Developer or on the Oracle DMBS?)

            2. If 1 is not on the client, maybe there's some database limit that is exceeded somewhere

            3. Some limit that is exceeded when the data is exported


            1 and 2 seems very unlikely as all the records successfully shows on the output pane, and I can go all the way to the bottom. So it ran successfully at least and returned the records to the client.

            • 3. Re: IN-Clause export issues
              Jeff Smith Sqldev Pm-Oracle

              Can you run the query multiple times without any problems?


              Unless you have fetched the entire resultset down, when you do the export it runs the query again.


              You could try to get around this issue using the formatter comments - add a /*csv*/ to your select and run with F5 - the data will come back automatically formatted in CSV.

              • 4. Re: IN-Clause export issues



                My session stays in tact. So I can run it for the exceeded list, see all the data on the screen, try and export, get a blank file, and run the query again.


                It's almost asif SQL dev thinks it's successfully exported it.


                I have to add, I run my SQL Dev program with the following batch program:

                c:\program files (x86)\java\jre7\bin\java.exe" -Xmx1024M -Xms128M -Xverify:none -Doracle.net.disableOob=true -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true -Dsun.java2d.ddoffscreen=false -Dwindows.shell.font.languages= -XX:MaxPermSize=128M -Dide.AssertTracingDisabled=true -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true -Djava.util.logging.config.file=logging.conf -Dsqldev.debug=false -Dide.conf="./sqldeveloper.conf" -Dide.startingcwd="." -classpath ../../ide/lib/ide-boot.jar oracle.ide.boot.Launcher


                This was mainly to get the disableOob flag in so that I can cancel queries and resume my session.


                Even with sqldeveloper.exe and sqldeveloperw.exe it doesn't work.