Oracle Entity Framework Core pass table parameter to stored procedure

Question

I am trying to pass a parameter to a stored procedure using the Oracle.EntityFrameworkCore package like this:

DataTable table = new DataTable();
table.Columns.Add("keyColumn", typeof(string));
table.Columns.Add("valueColumn", typeof(string));

var row = table.NewRow();
row.ItemArray = new object[]
{
    entry.KeyColumn,
    entry.ValueColumn
};

table.Rows.Add(row);

var parameter = new OracleParameter("entries",table);
parameter.UdtTypeName = "entry_type_list";


return context.Database.ExecuteSqlCommandAsync(
    new RawSqlString( @"EXEC set_entry_list (:entries)" ),
    parameter);

The stored procedure and type are defined like this:

CREATE OR REPLACE TYPE entry_type AS OBJECT
(
"keyColumn" NVARCHAR2(3), 
"valueColumn" NVARCHAR2(3)
);

CREATE OR REPLACE TYPE entry_type_list AS TABLE OF entry_type;

CREATE OR REPLACE PROCEDURE set_entry_list (entries entry_type_list) AS
BEGIN
REM Doing stuff
END;

But I get an error:

System.ArgumentException: Value does not fall within the expected range.
at Oracle.ManagedDataAccess.Client.OracleParameter..ctor(String parameterName, Object obj)

The only sources for this is an answer how to do this with SQL Server, but no answer for Oracle with EFCore. The issue here seems to be that Oracle only accepts an OracleParameter whereas others use SqlParameter.

If I use the SqlParameter type like this:

var parameter = new SqlParameter("entries", SqlDbType.Structured);
parameter.TypeName = "entry_type_list";
parameter.Value = table;

I get this error:

System.InvalidCastException: Unable to cast object of type'System.Data.SqlClient.SqlParameter' to type 'Oracle.ManagedDataAccess.Client.OracleParameter'.

I also did try setting parameter.OracleDbType to different values like Blob, RefCursor, Clob or XmlType, setting parameter.DbType to Object or setting CollectionType to PLSQLAssociativeArray with no success. Also passing a list or an array of objects instead of a table did not succeed.

I currently have no idea what else I could try.

Any method to pass a big amount of entities to a stored procedure in a performant way would help. I use them with the merge-command so I need to be able to convert those parameters to a table.

`SqlParameter` is part of the `SqlClient` library, which is for Microsoft **SQL Server** exclusively. — marc_s, Nov 08 '19 at 17:31

Christoph Sonntag · Accepted Answer · 2019-11-14T10:18:04.097

I now found a solution using a temporary table and using this one as my input parameter.

As I can't pass a complete table, but an array of simple objects I have to fill this table by passing one array for each column:

var keyColumn = new OracleParameter( "keyColumn", OracleDbType.Decimal );
keyColumn.Value = values.Select( c => c.KeyColumn).ToArray();
var valueColumn = new OracleParameter( "valueColumn", OracleDbType.Decimal );
valueColumn = values.Select( c => c.ValueColumn).ToArray();

using ( var transaction = this.dbContext.Database.BeginTransaction( IsolationLevel.ReadCommitted) )
{
    var connection = this.dbContext.Database.GetDbConnection() as OracleConnection;
    OracleCommand cmd = connection.CreateCommand();

    cmd.CommandText = @"
        INSERT INTO TMP_TABLE 
        (
        ""keyColumn"",
        ""valueColumn""
        )
        VALUES (
        :keyColumn,
        :valueColumn)";
    cmd.Parameters.Add( keyColumn ); 
    cmd.Parameters.Add( valueColumn );
    cmd.ArrayBindCount = values.Length;
    var insertCount = await cmd.ExecuteNonQueryAsync();


    cmd = connection.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "dbo.stored_procedure";

    var result = await cmd.ExecuteNonQueryAsync();
    transaction.Commit();
}

I created the temp table like this:

CREATE
GLOBAL TEMPORARY TABLE "dbo"."TMP_TABLE"
ON COMMIT DELETE ROWS
 AS SELECT * FROM "dbo"."REAL_TABLE" WHERE 0=1;

And changed my stored procedure to use it:

CREATE OR REPLACE PROCEDURE stored_procedure AS 
BEGIN
    REM use the "dbo"."TMP_TABLE"
END;

This answer helped me with the approach of bulk inserting with one array per column. The thread also contains some further discussion about the topic and a more generic approach.

I have couple good answers on this subject. But yes, oracle works completely different from SQL server (client soft) — T.S., Nov 15 '19 at 01:48
I'm a bit overwhelmed by the amount of answers you have. Do you have anything special in mind / do you think there is a better approach than what I did? — Christoph Sonntag, Nov 15 '19 at 08:45

Oracle Entity Framework Core pass table parameter to stored procedure

1 Answers1