SQLProvider does not work with Oracle on .NET 5

[jonny-keen]

SQLProvider will not compile when attempting to connect to an Oracle database on .NET 5

To Reproduce Steps to reproduce the behavior:

1. Using Visual Studio 2019, create a new F# Class library targeting .NET 5.0

2. Change the .fsproj file contents to the following:

   
   <Project Sdk="Microsoft.NET.Sdk">
   
   <PropertyGroup>
   <TargetFramework>net5.0</TargetFramework>
   <GenerateDocumentationFile>true</GenerateDocumentationFile>
   <WarnOn>3390;$(WarnOn)</WarnOn>
   </PropertyGroup>
   
   <ItemGroup>
   <Compile Include="Library.fs" />
   </ItemGroup>
   
   <ItemGroup>
   <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="3.21.3" />
   <PackageReference Include="SQLProvider" Version="1.2.9" />
   </ItemGroup>

3. Change the automatically generated `Library.fs` file contents to the following, ensuring to replace `"your connection string"` with a valid connection string.

```F#
namespace ClassLibrary1

open FSharp.Data.Sql

type Sql =
    SqlDataProvider<
        Common.DatabaseProviderTypes.ORACLE,
        ConnectionString="your connection string here">

4. Attempting to build the project gives the follwoing error: error FS3033: The type provider 'FSharp.Data.Sql.SqlTypeProvider' reported an error: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.Details: 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.Current execution platform: .NETFramework,Version=v4.7.2

Expected behavior The project should build successfully

[Thorium]

Oracle drivers are loaded via reflection so you need to copy the dependencies (like System.Text.Json.dll) to a path and set static parameter ResolutionPath to point there. https://fsprojects.github.io/SQLProvider/core/parameters.html#ResolutionPath Sadly .NET reflection cannot auto-load Nuget packages.

That being said, Oracle is very hard to test as I've never found a free little test Oracle to test the connection.

[jonny-keen]

Thanks for the tip, I'll try it out.

With regards to free test oracle, the official oracle docker image is free for development purposes: https://hub.docker.com/_/oracle-database-enterprise-edition

[jonny-keen]

I did manage to get this working eventually.

The fix was to manually copy the required .dll's into a folder within my project as suggested above:

• Oracle.ManagedDataAccess 21.3.0 (from https://www.nuget.org/packages/Oracle.ManagedDataAccess/)
• System.Text.Json 5.0.2 (targeting netstandard 2.0)
• System.DirectoryServices.Protocols 5.0.0 (targeting netstandard 2.0)

The directory structure is now:

ClassLibrary1.sln
ClassLibrary1
│   Library.fs
└───SQLProviderDependencies
         Oracle.ManagedDataAccess.dll
         System.DirectoryServices.Protocols.dll
         System.Text.Json.dll

and the code is as follows:

namespace ClassLibrary1

open FSharp.Data.Sql

[<Literal>]
let ResPath = @".\SQLProviderDependencies"

type Sql =
    SqlDataProvider<
        Common.DatabaseProviderTypes.ORACLE,
        ConnectionString="your connection string here",
        ResolutionPath=ResPath>