In my Oracle Database 12c2, I defined this UDT.
CREATE TYPE TAFARRAY AS VARYING ARRAY (1000000) OF VARCHAR2(2000);
Then, I created a procedure and used this UDT as an input parameter.
CREATE OR REPLACE PROCEDURE MYPROC(arrFirstName TAFARRAY)
AS
countUpdateRecords NUMBER(22,0) := 0;
BEGIN
loggerAutonomousCommit('Test - arrFirstName ',arrFirstName(1));
END MYPROC;
To test the procedure, I use this sql statement and it works as my expected.
DECLARE
arr TAFARRAY := TAFARRAY('one', 'two', 'three');
BEGIN
MYPROC(arr);
END;
Then, I create this C# method to pass an array of string to the procedure.
public async Task<string> ExecuteMyProcAsync()
{
string[] param = new string[] { "one", "two", "three" };
string msg = string.Empty;
OracleConnection oracleConnection = (OracleConnection)this.Context.Database.Connection;
if (oracleConnection.State != ConnectionState.Open)
oracleConnection.Open();
using (OracleCommand oracleCommand = oracleConnection.CreateCommand())
{
try
{
oracleCommand.CommandText = "MYPROC";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.BindByName = true;
oracleCommand.Parameters.Add(CreateArrayParameter("arrFirstName", param));
await oracleCommand.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
msg = ex.Message;
}
}
if (oracleConnection.State != ConnectionState.Closed)
{
oracleConnection.Close();
}
return msg;
}
private OracleParameter CreateArrayParameter(string paramName, string[] paramValues)
{
OracleParameter param = new OracleParameter
{
ParameterName = paramName,
OracleDbType = OracleDbType.Varchar2,
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = paramValues,
Size = paramValues.Length,
Direction = ParameterDirection.Input
//UdtTypeName = "TAFARRAY"
};
return param;
}
But, it throws an exception when executing the procedure.
Oracle.ManagedDataAccess.Client.OracleException: 'ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored'
I tried using UdtTypeName = "TAFARRAY"
, but still got the same error.
I use Oracle.ManagedDataAccess.Client v4.122.21.1
.
What am I missing in the C# code? Thanks!