Powershell connection to Oracle database
I am having problems connecting to Oracle Database from Powershell using Oracle.ManagedDataAccess.dll
.
I followed this Technet tutorial and got the following code:
add-type -path "C:\oracle\product\12.1.0\client_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll"
$username = "XXXX"
$password = "XXXX"
$data_source = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX))(CONNECT_DATA = (SERVER=dedicated)(SERVICE_NAME=XXXX)))"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"
try{
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)
$con.Open()
} catch {
Write-Error ("Cant open connection: {0}'n{1}" -f '
$con.ConnectionString, $_.Exception.ToString())
} finally{
if ($con.State -eq ‘Open) { $con.close() }
}
Unfortunately I am getting this error:
C:\Users\XXXX\Desktop\oracle_test.ps1 : Cant open connection: User Id=XXXX;Password=XXXX;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXX)(PORT=XXXX))(CONNECT_DATA =
(SERVER=dedicated)(SERVICE_NAME=XXXX)))
System.Management.Automation.MethodInvocationException: Exception calling "Open" with "0" argument(s): "The type initializer for 'Oracle.ManagedDataAccess.Types.TimeStamp' threw an exception." --->
System.TypeInitializationException: The type initializer for 'Oracle.ManagedDataAccess.Types.TimeStamp' threw an exception. ---> System.Runtime.Serialization.SerializationException: Unable to find assembly
'Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=XXXX'.
at OracleInternal.Common.OracleTimeZone.GetInstance()
at Oracle.ManagedDataAccess.Types.TimeStamp..cctor()
--- End of inner exception stack trace ---
at OracleInternal.ConnectionPool.PoolManager'3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, String instanceName)
at OracleInternal.ConnectionPool.PoolManager'3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
at OracleInternal.ConnectionPool.OracleConnectionDispenser'3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
at CallSite.Target(Closure , CallSite , Object )
--- End of inner exception stack trace ---
at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
at System.Management.Automation.Interpreter.ActionCallInstruction'2.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,oracle_test.ps1
- I have verified that the connection string is correct (it works for
tnsping
) - Username and password are also correct
- I replaced everything that should not be revealed with XXXX
- Database version: Oracle Database 12c Release 12.1.0.1.0
- The error is the same for 32-bit and 64-bit Powershell instances.
- Before the first launch it
Oracle.ManagedDataAccess.dll
does not load, after launch it remains loaded until I getOracle.ManagedDataAccess.dll
this shell.
Unfortunately I am neither Oracle nor a Powershell expert (I prefer MySQL and Python), so I would greatly appreciate any ideas and / or ideas you might have.
source to share
I'm not sure if this is a technical solution - I would classify it more as a workaround, but it worked for me.
After some research, I found a suitable alternative Oracle.ManagedDataAccess.dll
. I found the System.Data.OracleClient class in the .Net Framework. (This requires an installed Oracle client, which luckily has a machine)
Here is an outline of the solution that worked for me:
add-type -AssemblyName System.Data.OracleClient
$username = "XXXX"
$password = "XXXX"
$data_source = "XXXX"
$connection_string = "User Id=$username;Password=$password;Data Source=$data_source"
$statement = "select level, level + 1 as Test from dual CONNECT BY LEVEL <= 10"
try{
$con = New-Object System.Data.OracleClient.OracleConnection($connection_string)
$con.Open()
$cmd = $con.CreateCommand()
$cmd.CommandText = $statement
$result = $cmd.ExecuteReader()
# Do something with the results...
} catch {
Write-Error ("Database Exception: {0}`n{1}" -f `
$con.ConnectionString, $_.Exception.ToString())
} finally{
if ($con.State -eq ‘Open’) { $con.close() }
}
source to share
It could very well be a problem with oracle 12.2.x
I had to add the following lines to the sqlnet.ora file on the database server to allow connections from old oracle clients: SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8 SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8
After adding, I could login with Oracle 10g and 11g client
source to share