Hi!
I use the DLL Oracle.ManagedDataAccess.dll from NuGet packages Oracle.ManagedDataAccess and Oracle.ManagedDataAccess.Core to insert GeoJSON data into an Oracle database.
Everythig works with Oracle.ManagedDataAccess, but I experience a problem with one row when using Oracle.ManagedDataAccess.Core.
You can use the following code to test this:
First part for Oracle.ManagedDataAccess:
Set-Location -Path $env:TEMP
Invoke-WebRequest -Uri https://www.nuget.org/api/v2/package/Oracle.ManagedDataAccess -OutFile package.zip -UseBasicParsing
Expand-Archive -Path package.zip -DestinationPath Oracle.ManagedDataAccess
Remove-Item -Path package.zip
Add-Type -Path .\Oracle.ManagedDataAccess\lib\net462\Oracle.ManagedDataAccess.dll
First part for Oracle.ManagedDataAccess.Core:
Set-Location -Path $env:TEMP
Invoke-WebRequest -Uri https://www.nuget.org/api/v2/package/Oracle.ManagedDataAccess.Core -OutFile package.zip -UseBasicParsing
Expand-Archive -Path package.zip -DestinationPath Oracle.ManagedDataAccess.Core
Remove-Item -Path package.zip
Add-Type -Path .\Oracle.ManagedDataAccess.Core\lib\netstandard2.1\Oracle.ManagedDataAccess.dll
Second part for both packes (please change the connection string to your needs):
$connection = [Oracle.ManagedDataAccess.Client.OracleConnection]::new('DATA SOURCE=DockerDatabases/XEPDB1;USER ID=geodemo;PASSWORD=Passw0rd!')
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = 'CREATE TABLE countries (name VARCHAR2(50), iso CHAR(3), geometry SDO_GEOMETRY)'
$null = $command.ExecuteNonQuery()
$geoJSON = Invoke-RestMethod -Method Get -Uri https://datahub.io/core/geo-countries/r/0.geojson
$kazakhstan = $geoJSON.features | Where-Object { $_.properties.ADMIN -eq 'Kazakhstan' }
$command = $Connection.CreateCommand()
$command.CommandText = 'INSERT INTO countries VALUES (:name, :iso, sdo_util.from_geojson(:geometry))'
$command.BindByName = $true
$parameter = $command.CreateParameter()
$parameter.ParameterName = 'name'
$parameter.Value = $kazakhstan.properties.ADMIN
$null = $command.Parameters.Add($parameter)
$parameter = $command.CreateParameter()
$parameter.ParameterName = 'iso'
$parameter.Value = $kazakhstan.properties.ISO_A3
$null = $command.Parameters.Add($parameter)
$parameter = $command.CreateParameter()
$parameter.ParameterName = 'geometry'
$parameter.Value = $kazakhstan.geometry | ConvertTo-Json -Depth 4 -Compress
$parameter.OracleDbType = 'CLOB'
$null = $command.Parameters.Add($parameter)
$null = $command.ExecuteNonQuery()
This example code only inserts the data for kazakhstan, as all the other countries work with both packages. See my full code (which uses some wrapper commands) here: https://github.com/andreasjordan/PowerShell-for-DBAs/tree/main/Oracle/Examples/Spatial
The thrown error is "ORA-40441: JSON-Syntaxfehler". But as it works with one of packages, I don't think the data is wrong.