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

sumesh0710 sumeshjose at gmail.com
Thu Sep 4 15:15:01 EDT 2008


Really!!..but how do i make work this stuff in my machine? What is svn?.I am
a newbie.


Daniel Morgan-3 wrote:
> 
> 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
> 
> 
>       
> _______________________________________________
> 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-tp19271280p19317995.html
Sent from the Mono - General mailing list archive at Nabble.com.



More information about the Mono-list mailing list