Geek asks and answers

Oracle Managed ODP.NET cannot find tnsnames.ora

My managed ODP.net webapp works locally, but when deployed to a server it does not work with an error:

"TNS: the listener is currently unaware of the requested service in the connection descriptor

From looking around, it seems that this is because it cannot get into the tnsnames.ora file.

I tried the following without success:

  • Placing the tnsnames.ora file (the same one that works locally) in the [oracle home] [product] ... \ network \ admin folder.
  • Configure the TNS_ADMIN parameter in the web.config section of the ODP managed port that points to an environment variable.
  • Configure the TNS_ADMIN parameter in the web.config section of the ODP managed port that points directly to the tnsnames.ora file.

On the server, an attempt to start tnsping gives a TNS-03502 error: message 3502 was not found; No message file for product = NETWORK, object = TNS

What am I missing?

+5
source share
4 answers

Try using a line that is independent of tnsnames.ora, for example:

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

Just add the tns_admin path to web.config or app.config and point it to the folder where the tnsnames.ora file should be stored.

<oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="tns_admin" value="E:\oracle11\product\11.2.0\client_1\network\admin" />
      </settings>
    </version>
</oracle.manageddataaccess.client>
+6
source

, .

It seems to me that, since it seems that ODP.net does not allow you to specify the path to the TNS file, then if you know the path to the file, just read the file programmatically and set it to the DataSource ConnectionStringBuilder field. Not an ideal but reasonable workaround.

0
source

I got the same thing as I did some regular expression in the TNSNAMES file. After you have executed the regular expression in the file, you can transfer it to an object in Powershell or C #

param($tnsnamesPath = 'c:\tns\tnsnames.ora',$username = 'user',$password = 'gotmehere', $connectionName = 'mustard', $query = 'Select sysdate from dual')
$simplySQLPath = (Get-Module -ListAvailable simplySQL).ModuleBase
if($simplySQLPath -and (test-path $tnsnamesPath -PathType Leaf) -and (![string]::IsNullOrEmpty($node)))
{
    [System.Reflection.Assembly]::LoadFile("$simplySQLPath\DataReaderToPSObject.dll") | OUT-NULL
    Import-Module SimplySql -Force
    $parsedTN = (get-content $tnsnamesPath -raw)  -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='  
    $splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)' 
    $tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
    $connstring = "$tnsnames;User Id=$username;Password=$password"
    try
    {
        Open-OracleConnection -ConnectionString $connstring -ConnectionName $connectionName
        $result = Invoke-SqlQuery -ConnectionName $connectionName -Query "$SQLQuery"
        Close-SqlConnection -ConnectionName $connectionName
    }
    catch
    {
        $_.exception
    }

}
Else
{
    if(!(test-path $tnsnamesPath -PathType Leaf -ErrorAction Ignore))
    {
        Throw "Check TNSnamesPath:  $tnsNamesPath"
    }
    else
    {
        Throw "Exeception SIMPLYSQL not found in module Path $($env:PSModulePath)"
    }
}
$result

I wrote this code here: https://powershellposse.com/2018/03/13/tnsnames-file-parsing/

0
source

More articles:


All Articles