How do you find out ConnectionState of Corelab connection

[Wendy]

Hi: For System.Data.OracleClient, there is a connectionstate that can be used to find out the connection state of an oracle connection. What about CoreLab, I don't seemed to find it.

Also, any example on passing back from a Oracle Stored Procedure a varray structure and a refcurrsor? I was able to get refcursor to pass back and a statuscode to pass back both at the same time, but is having trouble passing back another "out" parameter that is of type "VARRAY".
Thanks

[John]

Hi;

I recall that we had some trouble getting the connection state. It turns out that the connection has a state property but it was not showing up properly in the Visual Studio 2003 intellisense. The intellisense would show something like ...GetState() when in fact the connection state is exposed as a property ...State.

I hope this helps!


John

[Oleg]

To represent VARRAY data type OraDirect .NET provides OracleArray class. You can obtain it from PL/SQL block or Stored Procedure in the following way:

Code: Select all

OracleConnection conn = new OracleConnection("UserId=scott;Password=tiger;Server=ora;");
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "begin\n :arr := tnumbers(1,2,3,4,5);\nend;";
cmd.Connection = conn;
cmd.Parameters.Add(new OracleParameter("arr", OracleDbType.Array, 0,
ParameterDirection.Output, true, ((System.Byte)(0)), ((System.Byte)(0)), "",DataRowVersion.Current, null, 0, "TNUMBERS"));
cmd.ExecuteNonQuery();
OracleArray arr = cmd.Parameters["arr"].Value as OracleArray;
foreach (object el in arr) {
 Console.WriteLine(el.ToString());
}

You should define TNUMBERS type in database as follows

CREATE TYPE TNUMBERS AS VARRAY(100) OF NUMBER

With REF CURSOR type you can work through OracleCursor class or get
OracleDataReader.

Please see OraDirect .NET reference for details.