[Mono-list] re f cursor issue in oracle stored procedure invoked from mono
Nagappan A
nagappan at gmail.com
Thu Sep 4 16:20:41 EDT 2008
Hello,
Some reference for you:
http://www.mono-project.com/AnonSVN
http://subversion.tigris.org/
Thanks
Nagappan
On Thu, Sep 4, 2008 at 12:15 PM, sumesh0710 <sumeshjose at gmail.com> wrote:
>
> 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.
>
> _______________________________________________
> Mono-list maillist - Mono-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-list
>
--
Linux Desktop (GUI Application) Testing Project -
http://ldtp.freedesktop.org
http://nagappanal.blogspot.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.ximian.com/pipermail/mono-list/attachments/20080904/386d1c72/attachment-0001.html
More information about the Mono-list
mailing list