PL/SQL Table as a parameter for a stored procedure

[wirving]

Hello,

I'm trying to pass an array to a stored procedure using almost the same method described here: http://www.devart.com/dotconnect/oracle ... Table.html

My stored procedure is:

create or replace procedure ARRAY_TEST_PROC
(
arr_in IN NUM_ARR,
response OUT NUMBER
)
AS
BEGIN
response := arr_in(1)+arr_in(2);
END;

Where NUM_ARR is a table of NUMBER.
And I try to call it with:

OracleConnection conn = ((EntityConnection)ctx.Connection).StoreConnection as OracleConnection;
conn.Open();

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "ARRAY_TEST_PROC";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("arr_in", OracleDbType.Number);
cmd.Parameters.Add("response", OracleDbType.Number, System.Data.ParameterDirection.Output);
cmd.Parameters["arr_in"].ArrayLength = 2;
cmd.Parameters["arr_in"].Value = new decimal[] { 1, 2 };

cmd.ExecuteNonQuery();
conn.Close();

But I got:

ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'ARRAY_TEST_PROC'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

What am I doing wrong?

Thanks,
Zoltan Tanczos

[Shalex]

Please try this code to send OracleTable to server:

Code: Select all

OracleConnection conn = ((EntityConnection)ctx.Connection).StoreConnection as OracleConnection;
conn.Open();

OracleTable tbl = new OracleTable(OracleType.GetObjectType("NUM_ARR", conn));
for (int i = 1; i < 3; i++) {
    tbl.Add(i);
} 

OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "ARRAY_TEST_PROC";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("arr_in", OracleDbType.Table).Value = tbl;
cmd.Parameters.Add("response", OracleDbType.Number, System.Data.ParameterDirection.Output);

cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["response"].Value);
Console.ReadKey();

An example of retrieving OracleTable object from server is available at http://www.devart.com/dotconnect/oracle ... Table.html.

[wirving]

Helo,

at this line:

OracleTable tbl = new OracleTable(OracleType.GetObjectType("NUM_ARR", conn));

I get:

ORA-03115 unsupported network datatype or representation

Note that I'm using the OracleConnection in Direct mode. I could only manage to use custom Oracle types using the following PL/SQL workaround:

private static string _plsqlString = @"
DECLARE
my_btt BASIC_TYPETAB := BASIC_TYPETAB();
BEGIN
FOR i IN 1..{0}
LOOP
my_btt.EXTEND;
my_btt(i) := BASIC_TYPEREC(:btt_id(i), :btt_value(i));
END LOOP;

{1}(my_btt);
END;";

And I execute it as CommandType.Text with - in this case - two array parameters:

cmd.CommandText = string.Format(_plsqlString, parameter.Length, procedureName);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("btt_id", OracleDbType.Number);
cmd.Parameters.Add("btt_value", OracleDbType.VarChar, 2000);
cmd.Parameters["btt_id"].ArrayLength = parameter.Length;
cmd.Parameters["btt_value"].ArrayLength = parameter.Length;

cmd.Parameters["btt_id"].Value = parameter.Select(p => p.Id).ToArray();
cmd.Parameters["btt_value"].Value = parameter.Select(p => p.Value).ToArray();
cmd.ExecuteNonQuery();

[Shalex]

The OracleTable class is not supported in Direct Mode.