How to write sqlnet.ora and tnsnames.ora for new database connection

Question

I just created a new database connection from Oracle SQL Developer called BATCHINSERT. But when I try to tnsping it I get the following error.

TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 27-APR-2016 15:28:37  

Copyright (c) 1997, 2014, Oracle.  All rights reserved.  

Used parameter files:  
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

This is my sqlnet.ora

# sqlnet.ora network configuration file in
# /opt/oracle/product/11.2.0/network/admin
NAMES.DIRECTORY_PATH=(TNSNAMES)
NAMES.DEFAULT_DOMAIN=WORLD
SQLNET.AUTHENTICATION_SERVICES = (NTS)

And this is my tnsnames.ora

BatchInsert=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SID = XE)
))

How to write the entry for it so I can run it from .bat file?

Answer 1

Your sqlnet.ora sets a default domain name of WORLD, so your tnsnames entry needs to have that too:

BatchInsert.WORLD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SID = XE)
))

You should then be able to tnsping and connect using either batchinsert or batchinsert.world.

Alternatively you can remove the NAMES.DEFAULT_DOMAIN=WORLD line from your sqlnet.ora, but you may have other things already that expect that to be set.

This has nothing to do with SQL Developer unless you want to use that TNS alias in a connection definition, rather than using a Basic connection. If you already have a TNS entry for XE you can just use that, without defining a new entry pointing to the same service/SID.

Answer 2

SQL Developer doesn't by default read SQLNet.ORA

Easy fix, in Preferences, point to the directory for your TNSNames files.

If you enable THICK Driver connections and have your $ORACLE_HOME os var set, then SQLDev should see and use your SQLNet.ORA file for connectivity stuff.