3

Is there a way to configure Oracle SQL Developer to respect the TNS look up order in sqlnet.ora ?

We have all information correctly configured in TNS_ADMIN folder:

ldap.ora
sqlnet.ora
tnsnames.ora

Our sqlnet.ora config TNS Look up order as:

...
NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES)
...

We want the client to check LDAP first , then local tnsnames.ora as a backup in case lost LDAP connection.

But looks like Oracle SQL Developer can not use sqlnet.ora.

You either have to select TNS or LDAP explicitly.

I google around , but no one seems bother with this.

Please let me know if it is even possible with Oracle SQL Developer, if so , how ?

LeY
  • 659
  • 7
  • 21

2 Answers2

7

For SQLNet.ORA to come into play, setup a THICK connection.

enter image description here

Once you have your HOME established, check 'Use OCI/Thick Driver' below that.

You'll be prompted to restart sqldev.

Then make your connection.

To confirm your connection is 'thick', run this in SQL worksheet with F5:

show connection

What comes back should show :OCI in the connection URL. If you see 'thin', you haven't successfully setup the THICK driver.

Otherwise by default SQL Developer uses JDBC to connect, and SQLNet.ora never comes into play. It can still look for and find a TNSNames.ora file though.

Run this in a worksheet to see what's what with TNS

enter image description here

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • My Oracle guru colleague told me a simple trick to solve this.I posted the answer myself below. With your explanation, I am curious now why the solution work if JDBC not friendly with sqlnet.ora, can you explain ? Thanks – LeY Oct 10 '18 at 20:25
  • 1
    @LeY JDBC is it's own driver - it doesn't need SQLNET.ORA which is part of an oracle client. However, if you want to force your java program to go through that, and pick up things from SQLNET.ORA, then the thick setup is the way to do it – thatjeffsmith Oct 10 '18 at 20:33
2

For people who also have trouble with this. Someone told me a simple solution.

enter image description here

LeY
  • 659
  • 7
  • 21
  • 1
    that forces OCI for the connection, and will work assuming we can figure out where your Oracle HOME is...if you get an error, go into the preferences like i show above and specify the home, and try the 'test' button – thatjeffsmith Oct 10 '18 at 20:22
  • @thatjeffsmith You mean in this case , if your lost LDAP connection , this will fail, am I right ? – LeY Oct 10 '18 at 20:27
  • @thatjeffsmith Sorry, I mixed up OCI with OID, what do you mean by OCI? Oracle Client Instance? by forces OCI , you basically mean it implicitly used the solution you posted, and figured out the Oracle Home from my environment (I don't have a %Oracle_Home% , I guess it is from environment variable %Path% or Registory ) , and I do see the difference from "show connection", now it says "***@jdbc:oracle:oci8:****", am I right ? – LeY Oct 10 '18 at 20:44
  • oracle call interface, OCI, yes - and jdbc:oracle:oci8 means you're now using sqlnet.ora for your connections – thatjeffsmith Oct 10 '18 at 20:51