I am trying to return an array of ids from an Oracle stored procedure to my c# code. I am getting the error "reference to uninitialized collection". Here is the simple PL/SQL proc :
create type mmm_type is table of number;
create or replace procedure test_app.mmm_test01 (id IN number, O_tab out mmm_type)
is
begin
for x in 1..1000 loop
O_tab(x) := to_char(x);
end loop;
end;
And here is my c# code to test:
using (OracleConnection connection = new OracleConnection(_connectionString))
{
using (OracleCommand cmd = new OracleCommand("mmm_test01", CommandType.StoredProcedure))
{
cmd.Connection = connection;
connection.Open();
OracleParameter inParam = cmd.Parameters.Add("id", OracleDbType.Int16, ParameterDirection.Input);
OracleParameter returnParam = cmd.Parameters.Add("O_tab", OracleDbType.Table, ParameterDirection.Output);
returnParam.ObjectTypeName = "mmm_type";
OracleDataReader dr = cmd.ExecuteReader(); //"reference to uninitialized collection" error here
I am new to Oracle and Devart. Any advice/input is appreciated. Thanks!
Help-Output Parameter of type table - c#
Re: Output Parameter of type table - c#
Here is a corrected code:
Code: Select all
create or REPLACE type mmm_type is table of number;
/
create or replace procedure mmm_test01 (id IN number, O_tab out mmm_type)
is
begin
O_tab := mmm_type(); -- by Devart
for x in 1..1000 loop
O_tab.EXTEND(); -- by Devart
O_tab(x) := to_char(x);
end loop;
end;
Code: Select all
using (OracleConnection connection = new OracleConnection("server=orcl1120;uid=alexsh;pwd=alexsh;")) {
using (OracleCommand cmd = new OracleCommand("mmm_test01", CommandType.StoredProcedure)) {
cmd.Connection = connection;
connection.Open();
OracleParameter inParam = cmd.Parameters.Add("id", OracleDbType.Int16, ParameterDirection.Input);
OracleParameter returnParam = cmd.Parameters.Add("O_tab", OracleDbType.Table, ParameterDirection.Output);
returnParam.ObjectTypeName = "mmm_type";
// OracleDataReader dr = cmd.ExecuteReader();
cmd.ExecuteNonQuery(); // by Devart
OracleTable table = (OracleTable)returnParam.Value; // by Devart
object[] values = new object[table.Count]; // by Devart
for (int i = 0; i < table.Count; i++) // by Devart
values[i] = table[i]; // by Devart
}
}
-
- Posts: 19
- Joined: Mon 04 Jan 2016 15:04
Re: Help-Output Parameter of type table - c#
This was very helpful. Thank you.