Working with collection

Working with collection

Postby k7e » Mon 10 Dec 2012 05:47

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);
                    }      
k7e
 
Posts: 3
Joined: Mon 10 Dec 2012 05:38

Re: Working with collection

Postby Pinturiccio » Tue 11 Dec 2012 15:50

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");
}
Pinturiccio
Devart Team
 
Posts: 1928
Joined: Wed 02 Nov 2011 09:44

Re: Working with collection

Postby k7e » Thu 13 Dec 2012 04:07

Pinturiccio, Thank you so much, it works!
k7e
 
Posts: 3
Joined: Mon 10 Dec 2012 05:38


Return to dotConnect for Oracle