You don’t need to have an Oracle Client installed on your machine to use Oracle SQL Developer. Everything you need to connect to Oracle Database is provided by the JDBC driver included with the download. So you can be up and running in usually less than 5 minutes.

But if you DO happen to have a Client on your machine, you’ll be happier.

If you’re too lazy to go read the above post, here it is in a nutshell:

  • JDBC doesn’t do everything that OCI can
  • OCI does a better job at reliably cancelling queries
  • OCI has better support for advanced data types including XML

So, if you’re on-board with the idea of connecting up your Oracle Client with SQL Developer, let’s talk about how to get that going.

Prior to version 4, you would tell SQL Developer to use the ‘OCI/Thick driver’ – by the way, OCI stands for ‘Oracle Client Interface.’

Yes or No, basically
Yes or No, basically

The ‘problem’ here is that many of you have more than one Client on your machine. So not being able to explicitly tell SQL Developer WHICH client to use was problematic.

So in version 4, we’ve enhanced the preferences to allow you to tell us exactly what client to use.

Now you can say what client you want us to use
Now you can say what client you want us to use

Note the actual PATH I have here is:
“Instant Client: file:/C:/Program Files/Oracle/11203_x64_InstantClient/instantclient_11_2/”

You can put the Instant Client anywhere on your machine, but we need the directory location of where we can find these files:

We get really picky, so you need to get this RIGHT.
We get really picky, so you need to get this RIGHT.

The Preferences

Use Oracle Client
This tells SQL Developer WHERE your client is. It will then use this location to attempt to load the OCI stuff when doing things like a Password Reset on an expired connection. It won’t cause SQL Developer to use the OCI/Thick driver for connections though.

Use OCI Thick Driver
This DOES tell SQL Developer to make OCI/Thick connections to your Oracle database. It will use the client specified in the previous preference.

SQL Developer will attempt to confirm that your Client is available once you click ‘OK.’ If it’s an Instant Client, it will check the OS Path to make sure it’s there. Note we can only look at the first item in the path, so you may get a false positive, in which case you can ignore by clicking ‘Yes’ here.

This just says, we tried to find this in the Path and failed - you can ignore IF you can connect
This just says, we tried to find this in the Path and failed – you can ignore IF you can connect

How Do I Know if I Did it ‘Right?’

You can check two things.

One – on a disconnected connection, right click. If you see ‘Reset Password’ available, you’re golden. Two, you can open Help > About and check the properties a la:

SQL Developer IS able to load the drivers, you win!
SQL Developer IS able to load the drivers, you win!

One Last Thing

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. At some point we’ll ship with a 12c driver, which means you’ll need a 12c client. But we’ll worry about that later 🙂

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

95 Comments

  1. Testing client directory … OK
    Testing loading Oracle JDBC driver … OK
    Testing checking Oracle JDBC driver version … OK
    Driver version: 11.2.0.3.0
    Testing testing native OCI library load … OK
    Success!

    after restarting sql developer reset password option not getting?

    What i am missing?

    • It looks good to me. What version of SQLDev are you using? And what do you have selected on the Advanced page of preferences?

  2. Thomas Rahn Reply

    Hi Jeff,

    I would like to specify the OCI thick driver for SQL Developer Data Modeler but the preferences there do not include the ability to do that. Is there a workaround, such as using the preferences from SQL Developer?
    Why?: our developers no longer use or know the schema passwords because their priveleges on each schema are tied to proxy users who are connected to the Windows AD. Works well with the thick driver, but not without.

    Thanks for the excellent blog.

    • I would run the modeler through SQL Developer to get the thick client connection support there, or try to form the proper jdbc url…

      jdbc:oracle:oci:@SID.WORLD

      Note: For thick client to work, the Oracle client library directory must be added to the library search path. Oracle client libraries are installed under $ORACLE_HOME/lib and ORACLE_HOME/lib32 directories. Add this directory to the library search path environment variable. The library search path environment for AIX is LIBPATH, for HP-UX is SH_LIB_PATH for Linux is LD_LIBRARY_PATH and for Windows is PATH.

      Got this off a blog from Oracle.com…I’ve not tested it.

  3. I am getting “oracle/jdbc/driver/GeneratedPhysicalConnection$1” when trying to connect.

    I am using Sql Developer 4.0.3
    I have Oracle Client 11.2.0.0 installed and trying to use Instant Client 11.2.0.3

    • The full error message –
      java.lang.NoClassDefFoundError: oracle/jdbc/driver/GeneratedPhysicalConnection$1
      at oracle.jdbc.driver.GeneratedPhysicalConnection.getSystemProperty(GeneratedPhysicalConnection.java:82)
      at oracle.jdbc.driver.GeneratedPhysicalConnection.getSystemPropertySqlTranslationProfile(GeneratedPhysicalConnection.java:46)
      at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:549)
      at oracle.jdeveloper.db.adapter.AbstractConnectionCreator.getConnection(AbstractConnectionCreator.java:185)
      at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.openConnection(RaptorConnectionCreator.java:348)
      at oracle.dbtools.raptor.standalone.connection.RaptorConnectionCreator.getConnection(RaptorConnectionCreator.java:85)
      at oracle.dbtools.raptor.dialogs.conn.ConnectionPrompt.promptForPassword(ConnectionPrompt.java:67)
      at oracle.jdevimpl.db.DBConnAddin$1.promptForPassword(DBConnAddin.java:78)
      at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:366)
      at oracle.jdeveloper.db.adapter.DatabaseProvider.getConnection(DatabaseProvider.java:299)
      at oracle.jdevimpl.db.adapter.CAConnectionCreator.createConnectionImpl(CAConnectionCreator.java:66)
      at oracle.javatools.db.DatabaseFactory.createConnection(DatabaseFactory.java:838)
      at oracle.javatools.db.DatabaseFactory.createDatabase(DatabaseFactory.java:282)
      at oracle.jdeveloper.db.DatabaseConnections.getDatabase(DatabaseConnections.java:833)
      at oracle.dbtools.raptor.utils.Connections$ConnectionInfo.getDatabase(Connections.java:175)
      at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1165)
      at oracle.dbtools.raptor.utils.Connections.getConnection(Connections.java:1139)
      at oracle.dbtools.raptor.navigator.db.DatabaseConnection.openConnectionImpl(DatabaseConnection.java:146)
      at oracle.dbtools.raptor.navigator.db.AbstractConnectionNode.getConnection(AbstractConnectionNode.java:30)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode.getObjectFactory(DatabaseTreeNode.java:83)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:138)
      at oracle.dbtools.raptor.navigator.db.impl.DatabaseTreeNode$LoadTask.doWork(DatabaseTreeNode.java:112)
      at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:554)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:745)

    • if you use the ‘test’ button the preferences dialog where you define the client, what does it show?

  4. Like yourself my company is switching from TOAD to SQL Developer.

    I checked my PATH for Oracle Home:

    User variable ORACLE_HOME C:\Oracle\product\12.1.0\client_1
    System variable ORACLE_HOME C:\oracle\oraclient11g

    Whether I choose either one of these locations as the Oracle Home Client Type I get the following errors:

    Testing the Oracle Home located at C:\Oracle\product\12.1.0\client_1
    Testing client directory … OK
    Testing loading Oracle JDBC driver … Failed:
    C:\Oracle\product\12.1.0\client_1\jdbc\lib\ojdbc6.jar is not a valid jar file

    Testing the Oracle Home located at C:\Oracle\OraClient11g
    Testing client directory … OK
    Testing loading Oracle JDBC driver … Failed:
    C:\Oracle\OraClient11g\jdbc\lib\ojdbc6.jar is not a valid jar file

    • Although that is the ORACLE_HOME path I selected, I changed the dropdown to “Instant Client” instead of “Oracle Home” and it worked… go figure.

  5. This is a great blog post! Thanks for all the details down to the files it needs to find. I figured out on one of our boxes that I have 11.2.0.1 Client so that must be our issue. Will hopefully get this sorted out soon. Thanks again for the great help!

  6. Hi Jeff,
    Please help. I am try to set my Oracle Home but I get the error as shown in the below. Appreciate your help and I do apologize for this trouble. I’m kind a newbie in SQL Developer. I am using Oracle SQL Developer 4 anyway.

    Validation Failed
    The client home C:\oracle\product\11.2_64\bin is not on the PATH.

    • Sounds like you added an instant client. For an instant client to be usable, the OS has to be able to find the binaries. This is done by adding that path to the OS Path environment variable. Go to your system preferences, find that variable, and put the client home directory FIRST in that list. Close and re-open SQL Developer and try again.

  7. I got the following error:
    “Testing the Instant Client located at D:\desenvolvimento\programas\sqldeveloper\instantclient_12_1
    Testing client directory … OK
    Testing loading Oracle JDBC driver … OK
    Testing checking Oracle JDBC driver version … OK
    Driver version: 12.1.0.1.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 D:\desenvolvimento\programas\sqldeveloper\instantclient_12_1 is present and precedes any other client installations.”

    I am running on Win7 64bits on 32bits JDK.
    Oracle SQL Developer 4.0.2.15
    Instant client for Win 64bits 12.1.0

    • Hi Jeff,
      I have same error with OCI library load Failed. I extracted the instance client and all files should be in the same folder. Not sure why it does not recognize the OCI driver. Is there environment variables that I need to set?

      Here is output:
      Testing client directory … OK
      Testing loading Oracle JDBC driver … OK
      Testing checking Oracle JDBC driver version … OK
      Driver version: 12.1.0.2.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:\Scott\Downloads\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1 is present and precedes any other client installations.
      java.library.path = C:\Scott\Downloads\sqldeveloper-4.1.1.19.59-x64\sqldeveloper\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\idmu\common;C:\Program Files\Dell\Dell Data Protection\Encryption\;.

    • Thanks for the response. I’m not very familiar with OS Path. Can you tell me where the OS Path and what is needed to put in?

    • OS = operating system
      PATH is an environment variable that Windows uses to define directories to search when looking for exe’s, dll’s, etc

      You’ll need admin privs on your machine

      If you don’t understand this, better to get your local admin to help

  8. Bill Bovard Reply

    Can SQL Developer support “break” statements? I have some queries that look nice in other tools but ugly, and no summation lines, in SQL Developer 3.0.
    Thanks,
    Bill

    • we don’t support ‘Break’ today – but our goal is to support 100% of the SQL*Plus syntax

  9. Using the OCI/Thick driver (Client Shared Library 64-bit – 11.2.0.3.0) under OSX does not appear to support the use of XMLELEMENT. If I use XMLELEMENT in SQLDeveloepr 4.01.14 connected via OCI the return is rows of null, change back to the standard JDBC driver and correct xml elements are returned – any suggestions/comments?

    • You could try a 11.2.0.4 or 12c client…but what’s the version of the database, and what happens if you query with SQL*Plus? I know there’s an issue or two with XML DB that was patched in 11.2.0.3/12 in the database.

    • BlackWoxs

      Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

      If run with SQL*Plus in the same folder as the instant client which is providing the OCI driver for SQL Developer the command works fine:

      SQL> select xmlelement(“Brand”,
      2 xmlelement(“Name”, brand_name),
      3 xmlelement(“Type”, brand_type))
      4 from
      5 brand;

      XMLELEMENT(“BRAND”,XMLELEMENT(“NAME”,BRAND_NAME),XMLELEMENT(“TYPE”,BRAND_TYPE))
      ——————————————————————————–
      FORESTERS BESTVALUE
      BINDER PRIMEPREMIUM
      STUTTENFURSTCONTRACTOR
      LE MODEPREMIUM
      HOME COMFORTCONTRACTOR
      OLDE TYME QUALITYCONTRACTOR ….. etc

      In SQL Developer with the OCI driver selected:

      > select xmlelement(“Brand”,
      xmlelement(“Name”, brand_name),
      xmlelement(“Type”, brand_type))
      FROM
      brand
      XMLELEMENT(“BRAND”,XMLELEMENT(“NAME”,BRAND_NAME),XMLELEMENT(“TYPE”,BRAND_TYPE))
      ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–

      13 rows selected

      It’s not a “biggie” just something to watch out for. When I can get a spare moment I’ll try a Instant Client update as you suggest.

    • And you might want to open an SR as well – they can give you much better exact information on the version conflicts and patches to get the XML stuff all happy.

  10. I really like using OCI in SQL Developer. I however had to get it to work in Debian linux-distro today. Using instant client I ran into issues with it not working and getting the typical error message about LD_LIBRARY_PATH. After a bunch of trial and error a colleague suggested that my problem was in lacking the libaio1 library. Dropping in it via apt-get it started working like magic.

    Just leaving a note about this here for other lost souls that need some help, or for the next time I try to find this via google. Trusting my leaking memory is a bad practice these days.

  11. Hi,

    I am trying to disable the autocheck for updates in Oracle SQL Developer 4.0.0.13, but I am not able to find it in preferences.

    Please could you help me on where I can find this option?

    Additionally please let me know if there is any other option which will enable the quick start of Oracle SQL Developer?

    Thanks,
    Nune

    • Help > Check for Updates

      Up top, uncheck ‘Automatically check…’

      How long is it taking you to startup the tool?

      What version of the JDK are you using?

      How large is your SQL History list?

  12. Hi Jeff, I just stumbled over your Site, searching for a solution for our strange “Change Password” Problem. We have used several different clients (Instant Clients, Fat Clients) in order to Change Passwords on 11.2.0.3 database in SQLDeveloper, but we had no luck, some combinations only let us change the password when we were connected, other behaviours were, that we were always getting the message “wrong username/password” while changing. Finally with Instant Client 12 we are at least able to change the password in general (when not connected to the database, what I think is the right behaviour). But nevertheless we are not able to change an expired password. Then we always get the message “Password has expired”…

  13. Hey Jeff, hi again!

    I’d like to inform you that I found a workaround for my problem. Indeed you were right, using multi-line comments helped me out, just like this:

    select *
    /* –doing this because I love my data */
    from dual;

    It’s kinda bizarre to me to comment inside a comment but turns out that the trigger accepted the inner comment in the end. Seems that I will be able to keep using SQLDeveloper.
    YAY! 😀

  14. In fact I’m part of the security folks now, this log system was developped by the dba team before I arrive, I don’t know exactly how it’s implemented but I know it uses triggers to check for a specific comment in every single DML or DDL fired in production environment. They had several meetings with the developers in order to define a standard for this and a request for change is out of question; there’s no use to a major change in a important part of the system just because one person.
    Thinking about it blew my mind now. Why the trigger is not blocking my commands if they reach the server without the propper comment?
    I mean, if I purposely ommit the comment the trigger get me an error, like it should do. If I put the comment I’m able to modify the database but the server itself did not get the comment because it was wiped out the command before getting in the server.
    In the log table I see my commands issued and none of them have the comment. Now I’m confused when are the triggers being fired. Seems that the check it in my own machine, before SQLDeveloper removes the comment.
    This is getting pretty weird, gotta investigate this tomorrow.

    • I’m sad to hear there’s only one person there using SQL Developer.

      I’m hoping you can continue to use it to do your development and your queries – perhaps build and test your DML there and copy to SQL*Plus for production?

      I agree that changing your rules for a single user is a bit much. I’ll try to let you know if we make an update here that will satisfy your auditing setup.

  15. Hi there, Jeff.
    I know this is not the place but I did not find the answer for my question anywhere so I thought that maybe you could help me out.
    I’m using sqldeveloper there’s long time and now I’m struggling with this problem: sqldeveloper 3.2 does not statement’s send the single line comments to the server. Seems that it removes anything after the “–” before sending it to the server itself. For exemple, I issue:
    select 1
    — comment
    from dual;
    When I look in the server, the statement I see is just
    select 1 from dual

    I’m currently working in a system that logs every single DML and the comments explaining the statement must be stored too, so I’m not able to use sqldeveloper since it’s just skipping the comments.
    I looked in every single option but I couldn’t find anything related to it. Is there an option somewhere in a config file or that’s a hardcoded behavior?

    • So we’re doing it on purpose, and it’s for a good reason – not that I expect you to care really, you need your comments!

      But, I have a decent work-around for you, give it a try.

      Instead of

      [sql]
      select *
      — doing this because I love my data
      from dual;
      [/sql]

      Do this

      [sql]
      select *
      /* doing this because I love my data */
      from dual;
      [/sql]

      And if you’re super curious as to why we’re doing it, when we send the statement via jdbc to the database, the entire statement gets concatenated to a single line. The ‘–‘ serves to wipe out the rest of the query when you do that.

    • Hmmm interesting, that explains why a hundred lines well indented query is so lame on enterprise manager.
      And yes, I did some tests with multi-line comments before and I was able to track them in the server, but unfortunately its not an option for me since the system I’m working with logs the dml with triggers that looks specifically for “–“; comments with /* */ are not accepted.
      Well, seems that I will have to put sqldeveloper aside, at least for production interventions.
      Just a curiosity, you said that it’s a jdbc issue, single line comments must be removed because the statement is concatenated in a single, right? So I presume that every jdbc statement has the same problem, not only in SQLDeveloper?

    • Leandro,

      Any chance your security folks would consider amending their trigger code to accept the other commenting style?

      In the mean-time, we’re looking to see if improvements in the JDBC layer can make this SQLDev behavior, ‘go away.’

  16. I think it’s time to go to the Forums or Support for help…but if you have the directory set as you say in the preferences, you should be good. The Oracle Installer updates the PATH for you, so you shouldn’t need to touch the PATH.

  17. Sorry, hate to keep bugging you.
    But, like I said, when I use the ‘C:\Oracle\client112_32’ directory the same problem exists.
    It even complains about the same directory (including bin) not being in the path.
    I just tried if adding the parent directory ‘C:\Oracle\client112_32’ to the path would make a difference, but nope.

  18. We look for the ‘bin’ directory UNDER the client path specified. I said we look for those files when doing an Instant Client. For Full Client installs, pick the root/home folder – not the BIN or bin.

  19. Uppercasing the bin doesn’t work.
    I hit ‘configure’ and the ‘configure oracle client’ window opens.
    I change ‘bin’ to ‘BIN’ and hit ‘OK’.
    I return to the preferences window and it’s changed back to ‘bin’

  20. mig$alg.haal_verdeling: gewijzigde out parameters

    I know, but the post says it’s looking for these oci* files. And they are in the bin directory.

    As far as I know OORACLE_HOME is not any directory within the client installation tree, its the praent directory of the database installation. Right?
    So, it did surprise me there were only 2 options: ‘instant client’ which I’m not using or Oracle Home, which to my knowledge should point to the database installation, not the client.

    However, choosing either option and using either the clients bin directory or it’s parent, all give me the same result.

    I’m beginning to fear that it doesn’t work with a standard client?
    That it has to be an instant client or the database installation directory?
    Correct?

    • Brad Simmons

      My full client install did not have the oci.sym file. (My foggy memory is suggesting there was an issue with this on some versions of the 11.2 client installs….) Anyways, I made sure the instant client directory (not including the ./bin directory) is in the PATH and then specified the instant client install & directory within SD4. OCI is alive!

    • Hi Jeff,
      I have the same issue, as Brad. I do not have the oci.sym file. What is the issue?
      My full oracle client version seems to be
      11.2.0.3.0 (From the header comment of SQL*Plus)

      Should suffice right?
      Thanks,
      Xenofon

    • Not sure, but SQL*Plus doesn’t really come into play. We need the oci binaries/drivers/libraries in order to make the connection over JDBC.

      Those files SHOULD be in the full and instant clients for 11.2.0.3 and 11.2.0.4

  21. Jeff,

    I’m having a bit of a challenge with this.
    I’m trying to set these preferences.
    But when I hit the ‘Ok’ button I do not get ‘your’ path message which you tell me I can ignore.
    However I get a ‘Validation failed’ screen with message “The client home C:\Oracle\client112_32\bin is not on the PATH”
    It just has an ‘Ok’ button, and clicking it just returns me to the preferences screen.

    I know you say “Note we can only look at the first item in the path”, so I was prepared for a message to ignore.
    But I can’t ignore it.
    Moving the entry in the path to be the first item (and rebooting just to be sure) doesn’t help.

    Any ideas?

    Same issue on 2 machines:
    SQL Developer : 4.0.0.13.80 (both)
    Oracle client : 11.2.0.1.0 / 12.1.0.1.0
    Oracle database : 11.2.0.3.0 / 12.1.0.1.0
    OS : Win 7 Enterprise SP1 64bit / Win 7 Home Premium SP1 64bit

    • Nope it’s not an instant client, it’s a standard client.
      When selecting the location I choose ‘Oracle home’ in stead of ‘Instant client’.

      I’m running 64 bit JDK 7u40

    • $ORACLE_HOME directory isn’t the \bin directory though, it’s the parent directory of that.

    • oh and try using uppercase BIN (in the path) – it’s a bug where we’re doing something weird with the validation check in the UI

    • hi Jeff,
      I’m having the same problem as Erik.

      I’m on a 32bit windows machine.

      I just downloaded Instant Client from http://www.oracle.com/technetwork/topics/winsoft-085727.html

      and set the path in SQL Developer. It’s showing:
      Instant Client: file:/C:/Oracle/product/instantclient_12_1/

      But hitting “OK” on the Preferences gives: “The client home C:\Oracle\product\instantclient_12_1 is not on the PATH”.

      I’ve added the same directory to the PATH system variable in System Properties.

      I’m able to start SQLPlus from the Instant Client no problem:
      C:\>
      C:\>cd C:\Oracle\product\instantclient_12_1
      C:\Oracle\product\instantclient_12_1>.\sqlplus
      SQL*Plus: Release 12.1.0.1.0 Production on Thu Jan 9 16:01:16 2014
      Copyright (c) 1982, 2013, Oracle. All rights reserved.
      Enter user-name:

  22. Jeff,

    Thanks for the post, but what do we gain in sql-developer switching from JDBC driver to OCI? I saw the below, but I don’t get it very clear.

    If you’re too lazy to go read the above post, here it is in a nutshell:
     JDBC doesn’t do everything that OCI can
     OCI does a better job at reliably cancelling queries
     OCI has better support for advanced data types including XML

    • The OCI bits have more listening points in the database code for cancel query requests. When you ask to cancel a query, you ask the database. The database has to see that request and respond to it, and then get around to canceling said query. If the query is running over JDBC, the database has fewer ‘listen’ points – I’m sure I’m mucking this up completely and the database developers would have kittens if they read this, but that’s as about as simple as I can put in in layman’s terms. So if you use OCI Thick connections vs straight-up JDBC connections, canceling queries in SQL Developer is more robust.

      Additionally, JDBC has poorer support for XMLDB than OCI. And, JDBC doesn’t support the ability to reset passwords when they expire on connection requests. So, having OCI Thick available is preferable. There are a few more edge cases, but the cancel query thing is probably enough for most people to get thinking about this.

  23. Hi. New version of the Oracle SQL Developer is beautiful, but it seems to me that the query execute more slowly. I ran the same query on a Oracle SQL Developer 4.0.0.13 and Oracle SQL Developer 3.2.20.09. In the first case the query is finished after 63.943, in the second case after 56.485 seconds. I repeated this exercise a few times and always older version was faster. Do you have any thoughts on this ? Thanks.

    • What does AutoTrace show for the query in 4.0?

      How many rows does the query return?

      HOw long does it take to run in SQL*Plus?

Reply To thatjeffsmith Cancel Reply