How install Oracle package for Oracle.ManagedDataAccess.dll in VS Code

Question

I'm trying to install the above package in VS Code. For some reason when I put this at the top of my script I get an error message.

using namespace system.collections.generic
Add-Type -AssemblyName System.Data.OracleClient
Add-Type  -Path "C:\Users\me\OneDrive - company\Documents\2021\temp endToEnd\oracle.ManagedDataAccess.Core\oracle.manageddataaccess.core.3.21.50\lib\netstandard2.1\Oracle.ManagedDataAccess.dll"

Error:

Add-Type : Missing an argument for parameter 'AssemblyName'. Specify a parameter of type 'System.String[]' and try again.

I also tried Add-type -Path "C:\Users\me\OneDrive - company\Documents\2021\temp endToEnd\oracle.ManagedDataAccess.Core\oracle.manageddataaccess.core.3.21.50\lib\netstandard2.1\Oracle.ManagedDataAccess.dll"

And it had this error:

Add-Type : Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

My question is, how do I load this, so I can do the following:

$connectionString = "Data Source=$dataSource;User Id=$username;Password=$password;" 
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)

I am trying to install this package because I have this error when I try to execute the last code line above:

New-Object : Cannot find type [Oracle.ManagedDataAccess.Client.OracleConnection]: verify that the assembly containing this type is loaded.

I tried doing it through nuget manager as well, with ctrl shift P, nuget manager, but it's not coming up in the list that I can see (odp.net, oracle managed..., etc).

I had download this from the oracle website:

oracle.manageddataaccess.core.3.21.50.nupkg

Then I used 7-zip to unzip it to the location I'm Add-Type from.

I've been looking at these links: New-object Oracle.ManagedDataAccess.Client.OracleConnection

oracle-developer-tools-vs-code

install nuget package in vs code

I can't seem to get this installed so the command works in the script. Any help would be appreciated.

Answer 1

You're basically treating powershell like a client application, you'll want: the Oracle Data Application Client(ODAC) driver, powershell is a managed memory model so you'll want the managed one, and most likely the 64-bit one unless for some reason you're running 32-bit powershell... Beyond that it'll likely depend on which version works best for your Oracle database.

For example 12cR1:

Download ODP.NET_Managed_ODAC122cR1.zip

extract odp.net\managed\common\Oracle.ManagedDataAccess.dll

PS C:\working> add-type -path (ls .\Oracle.ManagedDataAccess.dll).FullName
PS C:\working> $OraEntry = '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=database.example.com)(Port=1234)))(CONNECT_DATA=(service_name=BigData)))'
PS C:\working> $con = [Oracle.ManagedDataAccess.Client.OracleConnection]::new()
PS C:\working> $con.ConnectionString = "Data Source=$OraEntry;User Id=$username;Password=$password"
PS C:\working> $con.Open()

If you don't know what the OraEntry should be you can likely copy it from your tnsnames.ora and/or check with your database admin

Answer 2

Have a look at How the Runtime Locates Assemblies

Most common .NET assemblies are loaded either from current directory or from Global Assembly Cache (GAC). The GAC takes precedence over locally stored files.

Check files in your downloaded package, there should be the OraProvCfg.exe. Use it for adding the dll into GAC and doing the configuration:

OraProvCfg.exe /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:"C:\Users\me\OneDrive - company\Documents\2021\temp endToEnd\oracle.ManagedDataAccess.Core\oracle.manageddataaccess.core.3.21.50\lib\netstandard2.1\Oracle.ManagedDataAccess.dll"