1

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() }
  • I found this reference – EdLipson Dec 23 '16 at 13:07
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.