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

[sumesh0710]

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.