Query Oracle using .net core throws exception for Connect Identifier

Question

I'm quite new to Oracle and never used that before, now, I'm trying to query an Oracle database from a .Net Core Web Application having nuget package oracle.manageddataaccess.core installed and using an alias as the Data Source but I'm receiving the following error:

If I use the full connection string the query will work correctly

ORA-12154: TNS:could not resolve the connect identifier specified

   at OracleInternal.Network.AddressResolution..ctor(String TNSAlias, SqlNetOraConfig SNOConfig, Hashtable ObTnsHT, String instanceName, ConnectionOption CO)
   at OracleInternal.Network.OracleCommunication.Resolve(String tnsAlias, ConnectionOption& CO)
   at OracleInternal.ConnectionPool.PoolManager`3.ResolveTnsAlias(ConnectionString cs, Object OC)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)

So, from a few links I could understand that there is a tsnnames.ora file which must contain the map between connect identifiers and connect descriptors. And that this file can be found at the machine on which the Oracle has been installed with the path ORACLE_HOME\network\admin.

Question is: Does the alias name that I'm using in my connection string which reads as Data Source: <alias_name>; User ID=<user>; Password=<password> need to be specified in the tsnnames.ora file? I don't have access to the machine where the Oracle database resides on otherwise I would have checked it earlier.

Here's the code snippet for more info: connection string and query values are mocked out

 public static string Read()
        {
            const string connectionString = "Data Source=TestData;User ID=User1;Password=Pass1";

            const string query = "select xyz from myTable";

            string result;

            using (var connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    var command = new OracleCommand(query) { Connection = connection, CommandType = CommandType.Text };
                    OracleDataReader reader = command.ExecuteReader();
                    reader.Read();
                    result = reader.GetString(0);
                }
                catch (Exception exception)
                {
                    Console.WriteLine(exception);
                    throw;
                }
            }

            return result;
        }

Is this enough or something else needs to be added/changed in here? Or probably the issue is with the tsnNames.ora file which might not contain the alias name here?

Thanks in advance

psaraj12 · Accepted Answer · 2020-03-27 16:56:21Z

1

For the Data source

 Data Source=TestData;User Id=myUsername;Password=myPassword;

Your tnsnames.ora probably should have the below entry

 TestData=(DESCRIPTION=
        (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)   (PORT=MyPort)))    
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)))

Since

 Data Source=TestData;User Id=myUsername;Password=myPassword;

is same as

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)  (PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

answered Mar 27, 2020 at 16:56

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query Oracle using .net core throws exception for Connect Identifier

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related