Forum Stats

  • 3,872,912 Users
  • 2,266,489 Discussions
  • 7,911,381 Comments

Discussions

Using Oracle.ManagedDataAccess.dll with PowerShell - How to set this up with NuGet?

AndreasJordan
AndreasJordan Member Posts: 3 Blue Ribbon

I'm frequently using Oracle.ManagedDataAccess.dll with PowerShell to access Oracle databases from PowerShell with the following code:

Add-Type -Path "$env:ORACLE_HOME\odp.net\managed\common\Oracle.ManagedDataAccess.dll"

This is possible by installing the oracle client up to version 19c or the ODAC up to version 12.

Now I wanted to use the oracle client 21c and I don't find a way to set this up. There are only NuGet packages and no ready to use dll anymore.

I don't have Visual Studio and I don't want to develop a new application - I just want to add that ddl into my PowerShell session to be able to connect to oracle from within than session.

Can someone point me to a "how to" or documentation that would help we? Thanks a lot.

Tagged:

Best Answer

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,153 Employee

    The NuGet package is just a zip file. Download the NUPKG file, rename the extension to .zip, and unzip. Navigate to the "lib" directory to find the DLL that you can then extract.

  • AndreasJordan
    AndreasJordan Member Posts: 3 Blue Ribbon

    Hi Alex,

    thanks for your answer. And by the way: I'm Andreas Jordan - my display name is just not yet updated.

    I already tried that, but was unable to load the dll with "Add-Type" in PowerShell. Error was "Add-Type : Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information." The .Exception.LoaderExceptions was "Could not load file or assembly 'System.Text.Json, Version=4.0.1.1, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified."

    I also tried with the latest NuGet package from nuget.org but had no success as there is only "System.Text.Json.6.0.0" included, but version 4 is still requested.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,153 Employee
    Answer ✓

    The JSON package requirement is bug 33684412. We're trying to resolve this in the next 21c update so that the DLL's JSON package requirement is updated.

  • AndreasJordan
    AndreasJordan Member Posts: 3 Blue Ribbon

    Many thanks for the quick support!

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,153 Employee

    After reviewing this issue, the Oracle team suggests the below workaround if you encounter the PowerShell Add-Type command error:

    • Ignore the error/warning given by Add-Type command and still can perform the Db operations.
    • If there's concern about the error appearing, then you can execute the below steps (with PS script included also below):


    • Download System.Text.Json dll having 4.7.1 version from NuGet and extract it to some folder. A typical folder location after extract would be "\system.text.json.4.7.1\lib\net461\System.Text.Json.dll".
    • Use command

    "[Reflection.Assembly]::LoadFrom(‘<path-to-dll\System.Text.Json.dll>’)"

    • Use Add-Type command for our latest managed or core ODP.NET DLL (e.g.

    "Add-Type -Path ‘<path-to-dll\Oracle.ManagedDataAccess.dll’>")

    • Execute DB operation scripts.


    Why this problem exists?

    Answer: Managed and core ODP.NET packages from version 21 added dependency for "System.Text.Json" package having 4.7.1 version. So when you access/load the latest ODP.NET DLL using PowerShell Add-Type command, it reads the assembly reference information in its manifest data about "System.Text.Json" DLL of 4.0.1.1 assembly version(4.7.1 package version) is required. Ultimately, this problem is a .NET design issue that only MS can fix.

    This problem is seen with ODP.NET, but also exists for other non-Oracle .NET assemblies.


    Why this is a .NET design issue that MS would need to fix? 

    Because the PowerShell Add-Type command was created by Microsoft, it does not look up the NuGet package dependency version. It uses the assembly manifest data of any DLL, which does the load time checking of the required DLL to be loaded first.

    Microsoft has not developed any automatic dependency resolution from assembly manifest reference information yet, so users first have to explicitly load the dependency DLL with the exact version using the Add-Type command.

    Such a dependency DLL load issue also exists for the "Microsoft.Data.SqlClient" NuGet package. Before loading the SqlClient DLL, its dependent DLL ("Microsoft.Identity.Client" with exact version) must be loaded. See https://gist.github.com/gwalkey/00fe9e353ac755e5278bd6d092f20746

    If the dependency is not loaded first, it will give a "Could not load file or assembly" error for "Microsoft.Data.SqlClient" DLL.


    PowerShell script:

    [Reflection.Assembly]::LoadFrom("C:\dlls\AssemblyLoadFrom\471\System.Text.Json.dll")

    Add-Type -Path "C:\dlls\AssemblyLoadFrom\Oracle.ManagedDataAccess.dll"

    $OraConn = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("Data Source = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<HOSTNAME>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<SERVICE NAME>))); User Id = <USER>; Password = <PW>;")

    $OraCmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand

    $OraCmd.FetchSize = $OraCmd.FetchSize * 64

    $OraCmd.Connection = $OraConn

    $OraCmd.CommandText = "select * from emp"

    $OraConn.Open()

    $reader = $OraCmd.ExecuteReader()

    if ($reader.Read()) {

    $result = $reader.GetString(0)

    write-host -ForegroundColor Green $result 

    } else {

    write-host -ForegroundColor Yellow $result "nothing returned"

    }