Rufen Sie Oracle gespeicherte Prozedur auf, die den Parameter OdcivarChar2List unter Verwendung von Dapper und Oracle Ma ⇐ C#

[Anonymous]

Ich versuche, eine von C# gespeicherte Orakel -Prozedur aus C# aufzurufen, die sys.odcivarChar2List (eine Tabelle von varchar2 ) als Eingabeparameter nimmt. /> ora-06550: Zeile 1, Spalte 7: PLS-00306: Falsche Nummer oder Argumente in Anruf an 'get_email_recipients_by_roles' < /p>
ora-06550: Zeile 1, Spalte 7: PLS-00306: falsche Nummer oder Argumente von Argumenten in "Get_EMAIL_MIPS ' />ORA-06550: line 1, column 7: PL/SQL: Statement ignored https://docs.oracle.com/error-help/db/ora-06550/

The stored procedure looks like this:

Code: Select all

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>
Ich habe versucht, es so zu nennen: < /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

Sieht so aus:
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?