Help-Output Parameter of type table - c#

Help-Output Parameter of type table - c#

Postby inspectorDev » Mon 04 Jan 2016 15:38

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!
inspectorDev
 
Posts: 19
Joined: Mon 04 Jan 2016 15:04

Re: Output Parameter of type table - c#

Postby Shalex » Mon 04 Jan 2016 17:50

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
    }
}
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: Help-Output Parameter of type table - c#

Postby inspectorDev » Mon 11 Jan 2016 15:43

This was very helpful. Thank you.
inspectorDev
 
Posts: 19
Joined: Mon 04 Jan 2016 15:04


Return to dotConnect for Oracle