Working with collection

[k7e]

Hi there! I need your help so much. We've got an object type,

Code: Select all

CREATE OR REPLACE TYPE abc."CUR_O" IS OBJECT(
    num number(12),
    dat date.......

collection,

Code: Select all

CREATE OR REPLACE TYPE abc."CUR_T" IS TABLE OF abc.cur_o;

and function.

Code: Select all

 Check(pL in abc.tune.light%type,
        pP in number,
        ErrorMsg out varchar2,
        missed_appts out abc.cur_t) return boolean;

what we need to do to get missed_appts's values (num number(12),dat date.......) in ASP.NET(C#)

(C#) code

Code: Select all

 using (OracleCommand myCommand = new OracleCommand("abc.z1.func", c))
                    {
                        myCommand.CommandType = CommandType.StoredProcedure;
                        myCommand.Parameters.Add("pl", k.light);
                        myCommand.Parameters.Add("pp", p);
                        myCommand.Parameters.Add("errormsg", OracleDbType.VarChar, ParameterDirection.Output);
                        myCommand.Parameters.Add("missed_appts", OracleDbType.Object, "abc.currzapis_t").Direction = ParameterDirection.Output;
                        myCommand.ExecuteNonQuery();
                        myCommand.ExecuteArray(0);
                        msg = Convert.ToString(myCommand.Parameters["errormsg"].Value);
                    }		

[Pinturiccio]

Your code samples contain some inconsistencies: under creation the type is called CUR_T, and under parameter definition the currzapis_t type is used; when creating the function you use 'Check', and in the code you write the name of the function func. So I will give an example very much like yours, with the corresponding scripts, in order to avoid inconsistencies when reading it:
Scripts for creating types and function:

Code: Select all

CREATE OR REPLACE TYPE CUR_O IS OBJECT(
    num number(12),
    dat date);

CREATE OR REPLACE TYPE CUR_T AS
    TABLE OF CUR_O;

CREATE OR REPLACE FUNCTION func(missed_appts out cur_t) return BOOLEAN AS
  BEGIN
    missed_appts:= CUR_T(CUR_O(5,to_date('03/23/2012 00:00:01','mm/dd/yyyy HH24:MI:SS')),
CUR_O(6,to_date('03/25/2012 00:00:01','mm/dd/yyyy HH24:MI:SS')));
RETURN TRUE;
  END;

And C# code for getting data from the missed_appts output parameter:

Code: Select all

OracleConnection conn = new OracleConnection("host=orcl1120;uid=scott;pwd=tiger");
conn.Open();
OracleCommand myCommand = new OracleCommand("SCOTT.func", conn);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("missed_appts", OracleDbType.Table, "cur_t").Direction = ParameterDirection.Output;
myCommand.Parameters.Add("return_value", OracleDbType.Boolean).Direction = ParameterDirection.ReturnValue;
myCommand.ExecuteNonQuery();
OracleTable table = (OracleTable)myCommand.Parameters["missed_appts"].Value;
            
for (int i = 0; i < table.Count; i++)
{
        OracleObject obj = (OracleObject)table;
        Console.Write("Num = "+obj["num"]+";\tdat = "+obj["dat"]+"\n");
}

[k7e]

Pinturiccio, Thank you so much, it works!