[Mono-list] re f cursor issue in oracle stored procedure invoked from mono

[Daniel Morgan]

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 <sumeshjose at gmail.com> wrote:

> From: sumesh0710 <sumeshjose at gmail.com>
> Subject: [Mono-list] re f cursor issue in oracle stored procedure invoked from mono
> To: mono-list at lists.ximian.com
> 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  -  Mono-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list