PL/SQL Table as a parameter for a stored procedure

PL/SQL Table as a parameter for a stored procedure

Postby wirving » Thu 13 Oct 2011 15:36

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
wirving
 
Posts: 2
Joined: Thu 13 Oct 2011 15:30

Postby Shalex » Tue 18 Oct 2011 14:48

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/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleTable.html.
Shalex
Devart Team
 
Posts: 7369
Joined: Thu 14 Aug 2008 12:44

Postby wirving » Wed 19 Oct 2011 07:53

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();
wirving
 
Posts: 2
Joined: Thu 13 Oct 2011 15:30

Postby Shalex » Wed 19 Oct 2011 12:14

The OracleTable class is not supported in Direct Mode.
Shalex
Devart Team
 
Posts: 7369
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle