8 Replies Latest reply on Oct 8, 2014 8:35 PM by Gary Graham-Oracle

    Cancel result-set scroll breaks the session

    KiwiAndrew


      I ran a simple Select query that returned >100k rows.  I clicked in the resultset grid, and later pressed Control-End to get to the bottom of the script - Doh

       

      Pressed the X to cancel the scroll, which seemed to work.

       

      Then tried running another query in the same script - now all I get is

       

      No more data to read from socket

       

      This is on a shared connection.

       

      Is this suppsoed to happen?  I get similar results all the time (cancel a query => no more use from the session) and it's very frustrating.

        • 1. Re: Cancel result-set scroll breaks the session
          Gary Graham-Oracle

          If you right-click on the node for your connection in the Connections navigator tree, is there a Reconnect item in the context menu?  That will depend on the particular version of SQL Developer in use.  Reconnect eases the pain of a broken connection, but whether your effort to cancel a long-running transaction works without breaking the connection may depend on the JDBC driver version and whether you have configured the product to use OCI/Thick rather than the Thin driver.

          • 2. Re: Cancel result-set scroll breaks the session

            Post your OS, Java, sql developer and JDBC versions.


            • 3. Re: Cancel result-set scroll breaks the session
              KiwiAndrew

              @Gary - yes I can reconnect, and it helps, but not for the unshared connections...

               

              Window 7 Enterprise

              Java 1.7.0_55

              SQL Developer 4.0.2.15 Build 15.21

               

              jdbc.libraryC:\Program Files (x86)\Oracle\SQL Developer 4.0.2.15\jdbc\lib\ojdbc6.jar

               

              According ThatJeffSmith I need a version 11 client to use the Thick Client, but I only have ocijdbc9.dll - my client is 9.0.2, and the machine is locked down by the company so I can't install anything.

               

              Does this mean I'm out of luck? 

              • 4. Re: Cancel result-set scroll breaks the session
                Gary Graham-Oracle

                Does "locked down by the company" mean you cannot install anything at all on the machine, or even run what you need from a flash drive?  Did you install SQL Developer 4.0.2, or did they?  Could you upgrade to 4.0.3 if you wished?

                 

                Anyway, we see that the 11.2 JDBC driver is compatible with database clients back to 9.0.1: Getting Started .  But if the database server version also goes that far back I'm not sure if a modern client-side configuration will solve your problem.  If you are allowed to connect a flash or other external drive to the client machine, it is fairly easy to install on it ...

                1. An 11.2.0.3 Oracle Instant Client (Instant Client downloads for Windows).

                2. The latest SQL Developer (if Instant Client is 32-bit, run SQL Dev with a 32-bit JDK.  If 64-bit, use a 64-bit JDK)

                3. Tell the sqldeveloper.conf to keep user settings on external drive, e.g.,  AddVMOption -Dide.user.dir=D:/SQLDev/.sqldeveloper

                4. To configure OCI/Thick, however, you do need to prepend the OS PATH environment variable with the Instant Client's location.

                That can arranged by creating a bat file that modifies PATH then runs the sqldeveloper.exe.  Keep in mind that multiple Oracle home / Instant Client homes are possible on the same machine, as are SQL Developer installs.  Possibly the company's policies are flexible enough to accommodate you in some way.  Luck is one thing and semi-artificial obstacles are another.  

                • 5. Re: Cancel result-set scroll breaks the session
                  KiwiAndrew

                  Does "locked down by the company" mean you cannot install anything at all on the machine, or even run what you need from a flash drive?  Did you install SQL Developer 4.0.2, or did they?  Could you upgrade to 4.0.3 if you wished?

                  Unfortunately, it means I can't run anything they haven't installed, including from a flash drive.  4.0.3 is probably some way off (I want it for SSH Tunnelling, as well - most of our work is via tunnels).

                   

                  From ThatJeffSmith:

                  SQL Developer 4 ships with an 11.2.0.3 JDBC driver. That means you’ll want at least an 11.2.0.3 Client for this to be compatible with SQL Developer. 11.2.0.1 or 11.2.0.2 won’t work.

                  My interpretation of this is that I'm screwed (to use a technical term).

                   

                  In spite of that, I have set "Use Oracle Client" and set the path to my 9.2 home (C:\Oracle\ora92), but the Test option gives me

                   

                  Testing the Oracle Home located at C:\Oracle\ora92

                  Testing client directory ... OK

                  Testing loading Oracle JDBC driver ... Failed:

                    C:\Oracle\ora92\jdbc\lib\ojdbc6.jar is not a valid jar file

                  Why would it be looking for ojdbc6.jar (ojdbc14.jar exists in the directory)?

                  • 6. Re: Cancel result-set scroll breaks the session
                    Gary Graham-Oracle

                    I sympathize -- you are a bit boxed in for now...

                    Why would it be looking for ojdbc6.jar (ojdbc14.jar exists in the directory)?

                    Various versions of ojdbc6.jar exist (see our shipping history below), and all are compatible with the Java 6 (JDK 1.6) API.  As Jeff said, our 4.0.x requires the one  built for the 11.2.0.3 DB client.

                     

                    If you open/unzip any of these ojdbc*.jar files, just look for a MANIFEST.MF file.  In the MANIFEST, you get information like...

                    1. SQL Developer 1.2.1 shipped with ojdbc14, for use with a minimum of JDK 1.4 and targeting a10.2.0.3 DB client.

                    2. SQL Developer 1.5.1 shipped with ojdbc5, for use with a minimum of JDK 1.5 and targeting a 11.1.0.6 DB client.

                    3. SQL Developer 3.0.x shipped with ojdbc6, for use with a minimum of JDK 1.6 and targeting a 11.2.0.2 DB client.

                    4. SQL Developer 3.2.x shipped with ojdbc6, for use with a minimum of JDK 1.6 (1.7) and targeting a 11.2.0.3 DB client.

                    5. SQL Developer 4.0.x shipped with ojdbc6, for use with a minimum of JDK 1.6 (1.8) and targeting a 11.2.0.3 DB client.

                    The 12.1 DB client ships with both an ojdbc6 and ojdbc7 jar.  Not sure which our next release will ship with.  Maybe both.  

                    • 7. Re: Cancel result-set scroll breaks the session
                      KiwiAndrew

                      OK - some progress:

                       

                      I found ojdbc6.jar in the SQL Developer 4.0.2.15\jdbc\lib directory and copied it from there to c:\Oracle\ora92\jdbc\lib. The Test button in SQL Dev now gives (first few lines removed since they're the same as before):

                       

                      Testing checking Oracle JDBC driver version ... OK

                        Driver version: 11.2.0.3.0

                      Testing testing native OCI library load ... Failed:

                        Error loading the native OCI library

                        The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that

                        the expected native library directory C:\Oracle\ora92\bin is present and precedes any other client installations.

                        java.library.path = C:\Program Files (x86)\Oracle\SQL Developer 4.0.2.15\jdk\jre\bin;C:\WINDOWS\Sun\Java\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Lenovo\Access Connections\;C:\Program Files\ThinkPad\Bluetooth Software\;C:\Program Files\ThinkPad\Bluetooth Software\syswow64;C:\Program Files (x86)\Micro Focus\RUMBA\;C:\Program Files (x86)\Micro Focus\RUMBA\System;C:\ORACLE\ora92\bin;C:\Program Files\TortoiseSVN\bin;C:\Program Files (x86)\IBM\Client Access\Emulator;C:\Program Files (x86)\IBM\Client Access\Shared;C:\Program Files (x86)\IBM\Client Access\;.

                      I see that the PATH entry names C:\ORACLE\ora92\bin. The directory is actually C:|Oracle - although Windows normally ignores case, does it have to be correct for Java?

                       

                      In the bin directory are

                       

                      oci.dll

                      ocijdbc9.dll

                      ociw32.dll

                      Windows 7 is 64-bit - is ociw32.dll going to cause an issue?  Have They installed mismatched versions?

                      • 8. Re: Cancel result-set scroll breaks the session
                        Gary Graham-Oracle

                        I found ojdbc6.jar in the SQL Developer 4.0.2.15\jdbc\lib directory and copied it from there to c:\Oracle\ora92\jdbc\lib

                        Good try, but not a good idea.  This hacked 9.2 home (not 9.0.2 as you indicated earlier) would still need ocijdbc11.dll, etc.  In other words, all the things that come with an 11.2.0.3 client.  I think any additional effort you make to resolve the broken connections issue would be best directed toward convincing your IT organization to provide an environment that supports OCI/Thick on an 11.2.0.3 DB client for the 4.0.3 release (since it has the improved, more convenient SSH support you covet).  It is really a matter of productivity (yours and perhaps that of others) and I think you have a strong business case.

                        although Windows normally ignores case, does it have to be correct for Java?

                        Windows 7 is 64-bit - is ociw32.dll going to cause an issue?

                        The case does not matter.  Window 64-bit runs 32-bit apps using SysWOW64 (Windows-On-Windows), so that's not an issue either.  I am not sure if Oracle 9.2 is certified on Windows 7 64-bit, but  I do confess to running 32-bit 11.2.0.1 server on 64-bit Windows 7 despite it not being certified.