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 <[EMAIL PROTECTED]> wrote: > From: sumesh0710 <[EMAIL PROTECTED]> > Subject: Re: [Mono-list] re f cursor issue in oracle stored procedure invoked > from mono > To: [email protected] > 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 > <[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 > > > > > > -- > 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 - [email protected] > http://lists.ximian.com/mailman/listinfo/mono-list _______________________________________________ Mono-list maillist - [email protected] http://lists.ximian.com/mailman/listinfo/mono-list
