Powershell odp.net errors.

[ADMaster]

Hello again,

Hopefully this is my last powershell post for a while. Once this issue is fixed, I'll be able to put the script into production.

 

On the server 2012 r2 box I run most of my scripts on I have the Oracle odac 12c installed and this has worked for years. I couldn't get it working on my own workstation so I used the odp.net dll from here.

https://www.oracle.com/technetwork/topics/dotnet/index-085163.html

using this guide

https://blogs.technet.microsoft.com/heyscriptingguy/2012/12/04/use-oracle-odp-net-and-powershell-to-simplify-data-access/

 

This worked great on my machine and no large install.

One of my scripts runs on another w10 box that acts like a server and it is now in production running great.

The other script needs to run on another 2012 r2 box. the odp.net dll fails to load, and the odac dll doesn't want to work either.

I tried odp.net on server 2016 too, but got the same errors.

Everything I've read suggests there are no dependencies.

 

Windows 10

Add-Type -Path C:\oracle\Oracle.ManagedDataAccess.dll

No errors.

Here is the code working on W10

[color=#000000][font=Consolas][color=#0000ff]$[/color][color=#001080]constr[/color] = [color=#a31515]"User Id=myuser;Password=mypass;Data Source=serverip:1521/mydb"[/color]
[color=#0000ff]$[/color][color=#001080]con[/color] = [color=#795e26]New-Object[/color] Oracle.ManagedDataAccess.Client.OracleConnection([color=#0000ff]$[/color][color=#001080]constr[/color])
   [color=#0000ff]$[/color][color=#001080]con[/color][color=#795e26].open[/color]()
[color=#0000ff]$[/color][color=#001080]sql[/color] = [color=#a31515]"sql select statements here"[/color]
[color=#0000ff]$[/color][color=#001080]command[/color] =  [color=#795e26]new-object[/color] Oracle.ManagedDataAccess.Client.OracleCommand( [color=#0000ff]$[/color][color=#001080]sql[/color],[color=#0000ff]$[/color][color=#001080]con[/color])
[color=#0000ff]$[/color][color=#001080]reader[/color]=[color=#0000ff]$[/color][color=#001080]command[/color][color=#795e26].ExecuteReader[/color]()
[/font][/color]

 

This is what I get when running this code on 2012 r2

[color=#b22222]Add-Type : Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.At .ps1:4 char:1+ Add-Type -Path "C:\Oracle\Oracle.ManagedDataAccess.dll"+ ~   
+ CategoryInfo          : NotSpecified: ( [Add-Type], ReflectionTypeLoadException    +
FullyQualifiedErrorId : System.Reflection.ReflectionTypeLoadException,Microsoft.PowerShell.Commands.AddTypeCommand  : 
Can't open connection: System.Management.Automation.PSArgumentException: Cannot find type [Oracle.ManagedDataAccess.Client.OracleConnection]: verify that the assembly containing this type is loaded.   at 
System.Management.Automation.MshCommandRuntime.ThrowTerminatingError(ErrorRecord errorRecord)    
+ CategoryInfo          : NotSpecified: ( [Write-Error], WriteErrorException    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,.ps1[/color]

 

Here is the slight code change required to use the odac version installed.

[color=#000000][font=Consolas][[color=#0000ff]Reflection.Assembly[/color]]::LoadFile([color=#a31515]"C:\oracle\client\product\12.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll"[/color])
[color=#0000ff]$[/color][color=#001080]con[/color] = [color=#795e26]New-Object[/color] Oracle.DataAccess.Client.OracleConnection([color=#0000ff]$[/color][color=#001080]constr[/color])
[color=#0000ff]$[/color][color=#001080]command[/color] = [color=#795e26]New-Object[/color] Oracle.DataAccess.Client.OracleCommand ( [color=#0000ff]$[/color][color=#001080]sql[/color],[color=#0000ff]$[/color][color=#001080]con[/color])
[/font][/color]

Here are the errors I get when running this version.

GAC    Version        Location     ---    -------        --------                                                                 

True   v2.0.50727 [color=#000000]C:\Windows\assembly\GAC_64\Oracle.DataAccess\2.122.1.0__89b483f429c47342\Oracle.DataAccess.dll [/color][color=#b22222]       

.ps1 : Can't open connection: System.Management.Automation.MethodInvocationException: Exception calling ".ctor" with "1" argument(s): 
"The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception." ---> System.TypeInitializationException: The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception. ---> 
Oracle.DataAccess.Client.OracleException: The provider is not compatible with the version of Oracle client   at Oracle.DataAccess.Client.OracleInit.Initialize()   at
Oracle.DataAccess.Client.OracleConnection..cctor()   --- End of inner exception stack trace ---   at System.Runtime.Remoting.RemotingServices.AllocateUninitializedObject(RuntimeType objectType)   at System.Runtime.Remoting.Activation.ActivationServices.CreateInstance(RuntimeType serverType)   at System.Runtime.Remoting.Activation.ActivationServices.IsCurrentContextOK(RuntimeType serverType, Object[] props, Boolean bNewObj)   at 
System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)   at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)   at System.Management.Automation.DotNetAdapter.AuxiliaryConstructorInvoke(MethodInformation methodInformation, Object[] arguments, Object[] originalArguments)   --- End of inner exception stack trace ---   at 
System.Management.Automation.DotNetAdapter.AuxiliaryConstructorInvoke(MethodInformation methodInformation, Object[] arguments, Object[] originalArguments)   at System.Management.Automation.DotNetAdapter.ConstructorInvokeDotNet(Type type, ConstructorInfo[] constructors, Object[] arguments)   at 
Microsoft.PowerShell.Commands.NewObjectCommand.CallConstructor(Type type, ConstructorInfo[] constructors, Object[] args)    + CategoryInfo          : NotSpecified: ( [Write-Error], WriteErrorException    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,.ps1[/color]

 

 

If I run the script that loads the dll like this

[Reflection.Assembly]::LoadFile("C:\oracle\client\product\12.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll")

on the 2012 r2 that normally runs my scripts it runs without error.

 

The version of oracle odac installed of both 2012 r2 servers is the same, so I feel it must be some sort of dependency, I just don't know where to look next.

 

Thanks,

[ADMaster]

This is a strange one. I switched to using version 4 instead of 2 and it is working.

It was difficult to troubleshoot as when I tried switching to 4 last week it failed. I think powershell was keeping conflicting stuff in memory.

If I run version 2 then version 4 without exiting powershell, it will continue to fail.

With a fresh session of powershell v4 seams to be working without issue.