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

[Daniel Morgan]

Ok.  I fixed your bug.  I just committed it to svn trunk.  It was revision 112250.

The bug was with an output parameter that is a ref cursor from a stored procedure when calling ExecuteReader.  ExecuteReader is used by DbDataAdapter to fill a DataSet.  

--- On Wed, 9/3/08, sumesh0710 <sumeshjose at gmail.com> wrote:

> From: sumesh0710 <sumeshjose at gmail.com>
> Subject: Re: [Mono-list] re f cursor issue in oracle stored procedure invoked from mono
> To: mono-list at lists.ximian.com
> Date: Wednesday, September 3, 2008, 5:37 AM
> Thanks for the reply. I tried using execute reader, but the
> outputparameter
> value was null. In my case I used 
> 
> cmdSp = New OracleCommand("test", con)
> cmdSp.CommandType = CommandType.StoredProcedure 
> I will try as ur code. Is your mono version is latest? I
> saw in release
> notes of mono 1.9.1, that there is ref cursor support,
> which means no cursor
> support in previous versions?
> 
> 
> Daniel Morgan-3 wrote:
> > 
> > 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
> > 
> > 
> >       
> > _______________________________________________
> > Mono-list maillist  -  Mono-list at lists.ximian.com
> > http://lists.ximian.com/mailman/listinfo/mono-list
> > 
> > 
> 
> -- 
> View this message in context:
> http://www.nabble.com/ref-cursor-issue-in-oracle-stored-procedure-invoked-from-mono-tp19271280p19285959.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