Home » SQL & PL/SQL » Client Tools » How to export tnsnames.ora from Sql Developer (Sql Developer 20.2, Windows 10)
How to export tnsnames.ora from Sql Developer [message #682060] Fri, 02 October 2020 07:53 Go to next message
orHooha
Messages: 4
Registered: April 2015
Junior Member
Hi,

I'm having trouble setting using tools other than Sql Developer, to run queries. I can't set up an odbc driver for oracle, because the 'Test Connection' never works, even though I can log in from Sql Developer.

Is there a way of exporting all of the settings in Sql Developer to the TNSNAMES.ORA file?

I'm probably filling in the TNSNAMES wrong. So if there's a way of converting, or just a chart that maps each setting in Sql Developer to the corresponding setting in TNSNAMES, that would help.


So e.g. one error I get when trying to connect via odbc (instaclient 19.6), is

SqlState 08004
Ora-12154
TNS: Could not resolve the connect identifier specified

(when trying to set up a System DSN)

, even though I can easily see this database from

Re: How to export tnsnames.ora from Sql Developer [message #682062 is a reply to message #682060] Fri, 02 October 2020 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
The default behavior for SQL Developer is to use JDBC; not TNS.

ora-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ora-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ora-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ora-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ora-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ora-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
 https://edstevensdba.wordpress.com/2018/09/19/troubleshooting-ora-12154/
Re: How to export tnsnames.ora from Sql Developer [message #682063 is a reply to message #682062] Fri, 02 October 2020 08:51 Go to previous messageGo to next message
orHooha
Messages: 4
Registered: April 2015
Junior Member
"The default behavior for SQL Developer is to use JDBC; not TNS."

Yes. Of course. Sql Developer is java-based. Damn it, that should have occurred to me, months ago.

Ok, thanks for the quick response, BlSw. (I don't want to abbreviate your alias as 'BS', because of the connotation)

BlackSwan wrote on Fri, 02 October 2020 09:35
The default behavior for SQL Developer is to use JDBC; not TNS.

ora-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ora-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ora-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ora-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ora-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ora-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
 https://edstevensdba.wordpress.com/2018/09/19/troubleshooting-ora-12154/
Re: How to export tnsnames.ora from Sql Developer [message #682067 is a reply to message #682062] Fri, 02 October 2020 13:22 Go to previous message
orHooha
Messages: 4
Registered: April 2015
Junior Member
Still having a little problem with defining the TNSNAMES.ora file. All of the sites seem to assume that I have oracle installed on the workstation, and an ORACLE_HOME environment variable defined.

Anyway, it doesn't matter that much. Just have one more question, regarding Oracle SQLcl -

*******************
Unlike SqlPlus, is Sqlcl jvm-based? Is it using Jars, behind the scenes?
*******************


If I can use the settings from Sql Deveoper, to login and write queries for Sqlcl, then I might not need to use any Windows odbc-based tools.

Alternatively, Sql Developer itself seems to have some command-line options. The main question would be how long it takes to execute those commmands, assuming Sql Developer is already open. But I'll figure that out, through trial & error.

So, my question is regarding Sqlcl, and whether it requires a TNSNAMES file, or if it works with the same style of settings (jdbc-based), as Sql Developer.


Thanks & Regards,






BlackSwan wrote on Fri, 02 October 2020 09:35
The default behavior for SQL Developer is to use JDBC; not TNS.

ora-12154 ALWAYS only occurs on SQL Client & no SQL*Net packets ever leave client system
ora-12154 NEVER involves the listener, the database itself or anything on the DB Server.
ora-12154 occurs when client requests a connection to some DB server system using some connection string.
TNS-03505 is thrown by tnsping & is same error as ora-12154 thrown by sqlplus or others.
The lookup operation fails because the name provided can NOT be resolved to any remote DB.
The analogous operation would be when you wanted to call somebody, but could not find their name in any phonebook.
The most frequent cause for the ora-12154 error is when the connection alias can not be found in tnsnames.ora.
The lookup operation of the alias can be impacted by the contents of the sqlnet.ora file; specifically DOMAIN entry.
TROUBLESHOOTING GUIDE: ora-12154 & TNS-12154 TNS:could not resolve service name [ID 114085.1]
 https://edstevensdba.wordpress.com/2018/09/19/troubleshooting-ora-12154/
Previous Topic: Format issue with NULL values
Next Topic: Toad debug strange behavior
Goto Forum:
  


Current Time: Mon Nov 30 14:23:40 CST 2020