DbDataAdapter uses ExecuteReader and the returned reader to populate the data
table. If you try your example with ExecuteReader and then try to get the data
reader from the parameter, it will fail because you will have DBNull.Value. At
least this is what you get on .net 2.0.
My advice is to use ExecuteNonQuery(), then get the data reader from the
parameter. Then manually load a data table.
Here is an example that works for me on .net 2.0 and mono:
// test.cs
// gmcs test.cs /r:System.Data.dll /r:System.Data.OracleClient.dll
using System;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
public class test
{
public static void Main(string[] args)
{
Console.WriteLine("Start.");
OracleConnection con = new OracleConnection("Data source=testdb;user
id=someuser;password=somepass");
con.Open();
OracleCommand cmdSp = con.CreateCommand();
cmdSp.CommandText = "BEGIN\n SP_TEST_REF_CURSOR(:crsdata); \nEND;\n";
DataSet ds = new DataSet();
OracleParameter oprCursor = new OracleParameter("crsdata",
OracleType.Cursor);
oprCursor.Direction = ParameterDirection.Output;
cmdSp.Parameters.Add(oprCursor);
cmdSp.ExecuteNonQuery();
OracleDataReader reader = (OracleDataReader)oprCursor.Value;
Console.WriteLine("Build DataSet from reader that holds a REF CURSOR");
DataTable schema = reader.GetSchemaTable();
DataTable newTable = new DataTable();
if (reader.FieldCount > 0)
{
foreach (DataRow row in schema.Rows)
{
string columnName = row["ColumnName"].ToString();
Type columnType = (Type)row["DataType"];
newTable.Columns.Add(columnName, columnType);
}
while (reader.Read())
{
object[] values = new object[newTable.Columns.Count];
reader.GetValues(values);
newTable.Rows.Add(values);
}
}
reader.Close();
Console.WriteLine("Add DataTable to DataSet");
ds.Tables.Add(newTable);
Console.WriteLine("Show Result");
Console.WriteLine("Columns in DataTable");
foreach (DataColumn column in ds.Tables[0].Columns)
{
Console.WriteLine(" ColumnName: " + column.ColumnName);
Console.WriteLine(" DataType: " + column.DataType.ToString());
}
Console.WriteLine("Rows in DataTable");
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int c = 0; c < ds.Tables[0].Columns.Count; c++)
{
Console.WriteLine("Column " + c.ToString());
if (row[c].Equals(DBNull.Value))
Console.WriteLine(" Value is DBNull.Value");
else
{
Console.WriteLine(" Value: " + row[c].ToString());
Console.WriteLine(" Value Type: " +
row[c].GetType().ToString());
}
}
}
con.Close();
con = null;
Console.WriteLine("Success!");
}
}
--- On Tue, 9/2/08, sumesh0710 <[EMAIL PROTECTED]> wrote:
> From: sumesh0710 <[EMAIL PROTECTED]>
> Subject: [Mono-list] re f cursor issue in oracle stored procedure invoked
> from mono
> To: [email protected]
> Date: Tuesday, September 2, 2008, 10:27 AM
> Hi,
> OS - Linux Version 2.6.9-42.0.0.0.1( Red hat version
> 3.4.6-3.1)
> Oracle - 9.2.0
> mono - 1.2.4
>
> I have a simple procedure which has a ref cursor as
> parameter as follows.
>
> CREATE OR REPLACE PROCEDURE
> "DARSTRAN"."TEST" (crsdata out
> sys_refcursor)
> as
>
> BEGIN
> open crsdata for select sysdate from dual;
> END;
>
> If i execute this from proc from sql plus. It works fine.
> Also in windows
> OS, if i call from an application developed in vb.net it
> will work. But same
> code executed in mono in Linux OS shows below error while
> filling into
> dataset.
> ora- 01403: no data found.
>
> Code used is
>
> cmdSp = New OracleCommand("test", con)
> cmdSp.CommandType = CommandType.StoredProcedure
> ds = New DataSet
> oprCursor = New OracleParameter("crsdata",
> OracleType.Cursor)
> oprCursor.Direction = ParameterDirection.Output
> cmdSp.Parameters.Add(oprCursor)
> dasp = New OracleDataAdapter(cmdSp)
> dasp.Fill(ds)
>
> If i execute above SP from linux sql plus, also it works
> ok.
>
> Is there any workaround to solve this problem?
> --
> View this message in context:
> http://www.nabble.com/ref-cursor-issue-in-oracle-stored-procedure-invoked-from-mono-tp19271280p19271280.html
> Sent from the Mono - General mailing list archive at
> Nabble.com.
>
> _______________________________________________
> Mono-list maillist - [email protected]
> http://lists.ximian.com/mailman/listinfo/mono-list
_______________________________________________
Mono-list maillist - [email protected]
http://lists.ximian.com/mailman/listinfo/mono-list
