Позвоните в хранимую процедуру Oracle, которая принимает параметр OdcivarCharChar2list из C# с помощью Dapper и Oracle у ⇐ C#

[Anonymous]

Я пытаюсь вызвать хранимую процедуру Oracle из C#, которая принимает sys.odcivarchar2list (таблица varchar2 ) в качестве параметров ввода.

Код: Выделить всё

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_EMAIL_RECIPIENTS_BY_ROLES'
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_EMAIL_RECIPIENTS_BY_ROLES'
ORA-06550: line 1, column 7: PL/SQL: Statement ignored https://docs.oracle.com/error-help/db/ora-06550/
< /code>
sp выглядит следующим образом: < /p>
CREATE OR REPLACE PROCEDURE get_email_recipients_by_roles(
p_to_roles IN SYS.ODCIVARCHAR2LIST,
p_cc_roles IN SYS.ODCIVARCHAR2LIST,
r_data OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN r_data FOR
SELECT '[email protected]' AS email, 'To' AS usage FROM dual;
-- Actual implementation here
END;
< /code>
Я попытался назвать это как: < /p>
-- Calling this in SQL Developer WORKS!
VAR v_cursor REFCURSOR;
BEGIN
get_email_recipients_by_roles(
p_to_roles => SYS.ODCIVARCHAR2LIST('ROLE1', 'ROLE2'),
p_cc_roles => SYS.ODCIVARCHAR2LIST('ROLE3'),
r_data => :v_cursor
);
END;
PRINT v_cursor;
< /code>
// Calling using Dapper FAILS!
public void CallWithDapper(string connectionString)
{
var toRoles = new[] { "ROLE1", "ROLE2" };
var ccRoles = new[] { "ROLE3" };

var parameters = new OracleDynamicParameters();
parameters.Add("p_to_roles", toRoles, OracleDbType.Varchar2, ParameterDirection.Input, size: toRoles.Length, collectionType: OracleCollectionType.PLSQLAssociativeArray);
parameters.Add("p_cc_roles", ccRoles, OracleDbType.Varchar2, ParameterDirection.Input, size: ccRoles.Length, collectionType: OracleCollectionType.PLSQLAssociativeArray);
parameters.AddCursorParameter("r_data", OracleDbType.RefCursor, ParameterDirection.Output);

using var connection = new OracleConnection(connectionString);
connection.Open();
// This line throws PLS-00306
var result = connection.Query("get_email_recipients_by_roles", parameters, commandType: CommandType.StoredProcedure).ToList();
}

// Calling using Oracle Command also FAILS!
public void CallWithOracleCommand(string connectionString)
{
var toRoles = new[] { "ROLE1", "ROLE2" };
var ccRoles = new[] { "ROLE3" };

using var connection = new OracleConnection(connectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "get_email_recipients_by_roles";

var toParam = new OracleParameter("p_to_roles", OracleDbType.Varchar2)
{
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = toRoles,
Size = toRoles.Length,
Direction = ParameterDirection.Input
};
command.Parameters.Add(toParam);

var ccParam = new OracleParameter("p_cc_roles", OracleDbType.Varchar2)
{
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = ccRoles,
Size = ccRoles.Length,
Direction = ParameterDirection.Input
};
command.Parameters.Add(ccParam);

var cursorParam = new OracleParameter("r_data", OracleDbType.RefCursor)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(cursorParam);

// This line throws PLS-00306
using var reader = command.ExecuteReader();
while (reader.Read())
{
var email = reader.GetString(0);
var usage = reader.GetString(1);
Console.WriteLine($"{email} - {usage}");
}
}
< /code>
OracleDynamicParameters

Посмотрите на это:
using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using Dapper;
using Oracle.ManagedDataAccess.Client;

public class OracleDynamicParameters : SqlMapper.IDynamicParameters
{
private readonly DynamicParameters dynamicParameters = new DynamicParameters();
private readonly List oracleParameters = new List();

public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)
{
dynamicParameters.Add(name, value, dbType, direction, size);
}

public void AddCursorParameter(string name, OracleDbType oracleDbType, ParameterDirection direction)
{
var oracleParameter = new OracleParameter(name, oracleDbType, direction);
oracleParameters.Add(oracleParameter);
}

// Overload for Oracle-specific parameters, including collection type
public void Add(
string name,
object value,
OracleDbType oracleDbType,
ParameterDirection direction = ParameterDirection.Input,
int? size = null,
OracleCollectionType? collectionType = null)
{
var param = new OracleParameter(name, oracleDbType)
{
Direction = direction,
Value = value ?? DBNull.Value
};

if (size.HasValue)
param.Size = size.Value;

if (collectionType.HasValue)
param.CollectionType = collectionType.Value;

oracleParameters.Add(param);
}
}
< /code>
What is the correct way to pass list/array from C# to an Oracle stored procedure?
Is there a known workaround or limitation in ODP.NET or Dapper for this scenario?

Подробнее здесь: https://stackoverflow.com/questions/797 ... om-c-sharp