ODP Connect String with Oracle Wallet

oraclepowershell

I'm trying to use ODP.NET in PowerShell to access an Oracle DB. I want to use my existing Oracle Wallet for the connection credentials but I find no hints about how to specify the connect string with a Wallet. I've tried a few variations but I'm just not getting it correctly specified.

SQLPlus would use /@

What is it for ODP.NET?

Searching for the solution, I found that ODP needs to be configured to find the wallet. I specified the following as app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <oracle.manageddataaccess.client>
    <version number="4.121.2.0">
      <settings>
        <setting name="WALLET_LOCATION" value="(SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = c:\Users\xeaa2ll\Oracle\Wallet\)))"/>
        <setting name="SQLNET.WALLET_OVERRIDE" value="true"/>
        <setting name="TNS_ADMIN" value="c:\Users\xeaa2ll\ora\product\11.2.0\client_1\network\admin\"/>
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

The code I'm testing is

$configPath = "app.config"
[System.AppDomain]::CurrentDomain.SetData("APP_CONFIG_FILE", $configPath)

Add-Type -Path "c:\Users\xeaa2ll\ora\product\12.1.0\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

$tns = 'OODBAP01'
$uid = 'xeaa2ll'
$pw  = 'xx'
$query = "select 1 from dual"
$connectionString = 'Data Source=/@oodbap01.bnymellon.net:1524:' + $tns  # fails
$connectionString = 'Data Source=/@' + $tns                              # fails
$connectionString = 'Data Source=' + $tns                                # fails
$connectionString = 'User Id = ' + $uid + '; Password = ' + $pw + '; Data Source=' + $tns
try {
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)
    $connection.open()
}
catch
{
    Write-Error ("Can't open connection: {0}`n{1}" -f `
    $connection.ConnectionString, $_.Exception.ToString())
}
finally {
    $command=$connection.CreateCommand()
    $command.CommandText=$query
    $reader=$command.ExecuteReader()
    while ($reader.Read()) {
        $val = $reader.GetValue(0) 
        write-host $val
    }
}
if ($connection.State -eq 'Open') { $connection.close() }

Best Answer

This works for me:

"User Id=/;Data Source=DBNAME"

Please note that many people (including myself) are having issues getting an Oracle Wallet to work with the 12c managed driver. You may need to enable SSL client authentication. See the following thread on the Oracle Community site for details.

https://community.oracle.com/thread/2590420

As an aside, I'd recommend not overriding the app.config for powershell.exe, and instead using the machine.config. If you don't see the entries in there to edit, run the configure.bat script found in ORACLE_HOME\odp.net\managed\(x64|x86)\

ise ([System.Configuration.ConfigurationManager]::OpenMachineConfiguration().FilePath)

And then in your Powershell script, you can also load the driver without having to know the path to the DLL:

[void][reflection.assembly]::Load("Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342")