Oracle Error: TNS: SERVICE_NAME in CONNECT_DATA 1 was not provided to the listener

Question

I found that Visual stutio 2010 failed when I try to connect to an Oracle database

http://i.stack.imgur.com/BtIKu.jpg

http://i.stack.imgur.com/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?

Answer 1

This is an old post here, but since I was in the same situation, and this forum is approaching the start of a Google search, I decided to post my solution.

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

The problem was the FQDN service name. He tried to solve this problem through EZCONNECT, but in Oracle 11g EZCONNECT does not send the service name at all.

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

 NAMES.DIRECTORY_PATH= (TNSNAMES) 

• In "$ ORACLE_HOME \ database \ network \ admin \ tnsnames.ora" create an additional section with the fully qualified domain name. How:

  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)))

• Use tnsping to use both names for ping: 1) tnsping example; 2) tnsping example.domain.com - both names should respond.

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

I hope this helps someone.

BR

Raul

Answer 2

You need to use the shortened version when setting the DataSource property of the Connection string. The entries in your TNSNames file will be transferred 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);