CODEWITHSUNDEEP
.netsqloracle-database
Solution
Solution

Reputation: 164

Oracle error: TNS: Listener was not given the SERVICE_NAME in CONNECT_DATA 1

I found that error Visual stutio 2010 when I try to connect with Oracle database

https://i.sstatic.net/BtIKu.jpg

https://i.sstatic.net/q6ffE.jpg

Here is TNSNAMES.ORA: 

TNS_ALIAS=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =188.11.32.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Here is sqlnet.ora

# sqlnet.ora Network Configuration File: F:\app\user\product\11.2.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

What should I do now??

Upvotes: 6

Views: 52624

Answers (3)

Niharika
Niharika

Reputation: 1

I faced similar issue while connecting to oracle instant client, 1) Issue while connecting to Oracle in Suse/Redhat machine use < export TWO_TASK=//xxxx.xxx.xxxx.xxxx:1521/service_name> xxx.xxx.xxx -> represents server name/address

here service name is the the DB created in Oracle and refered in tnsnames.ora file

Below is the content of tnsnames.ora file ORDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server_name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORDB) ) )

2) Same issue is resolved differently in Solaris Sparc machine.

Update the ../odbc.ini file

[dsn_name]

add ServerName parameter

Upvotes: 0

Rebecca Hansen
Rebecca Hansen

Reputation: 41

You need to use the shorthand version when setting the DataSource property of the Connection string. The entries in your TNSNames file will translate to this

var conBuiler = new OracleConnectionStringBuilder();
//DataSource = "HOST:PORT/SERVICE_NAME"
conBuilder.DataSource = "example.domain.com:1521/x99.domain.com"
conBuilder.UserId = "SomeUser";
conBuilder.Password = "Password123";

var orCon = new OracleConnection(conBuilder.ConnectionString);

Upvotes: 4

Raul Adler
Raul Adler

Reputation: 56

It is old post here but as I was in same situation and this forum comes up pretty top of google search then I decided to post my solution.

I tried to send XML request to Oracle server and got from one instance: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

The problem was in FQDN service_name. It tried to solve it over EZCONNECT but in Oracle 11g EZCONNECT does not send Service name at all.

Solution: 1. In "$ORACLE_HOME\database\network\admin\sqlnet.ora" use only TNSNAMES in NAMES.DIRECTORY_PATH like:

NAMES.DIRECTORY_PATH= (TNSNAMES)

  1. In "$ORACLE_HOME\database\network\admin\tnsnames.ora" create a additional section with FQDN. Like:

    EXAMPLE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = example.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = x99.domain.com) (SID=X) ) )

    EXAMPLE.DOMAIN.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = example.domain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = x99.domain.com) (SID=X) ) )

  2. Use tnsping utilite to ping both names: 1) tnsping example; 2) tnsping example.domain.com - both names must answer.

NB! Use your own HOST, SERVICE_NAME AND SID of cource ;)

I hope that it helps someone.

BR

Raul

Upvotes: 4

Related Questions