Help-Output Parameter of type table - c#

[inspectorDev]

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!

[Shalex]

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
    }
}

[inspectorDev]

This was very helpful. Thank you.