This discussion is archived
1 Reply Latest reply: Sep 6, 2013 2:01 AM by Alex_Keh - Oracle_Product_Manager RSS

Managed ODP.NET  error ORA-01001: Invalid cursor

user10444249 Newbie
Currently Being Moderated

I'm having a problem with managed ODP.NET

We are using OracleDataAdapter and RefCursor to fill DataTable in blocks.

I created a small example that produces this error:

 

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
namespace OracleManagedTest
{
    class Program
    {
        private static string GetOracleDataAccessVersion()
        {
            try {
                Assembly a = Assembly.GetAssembly(typeof(OracleCommand));
                if (a != null) return a.GetName().Version.ToString();
            } catch (Exception ex) {
                return ex.Message;
            }
            return "unknown";
        }
        static void Main(string[] args)
        {
            Console.WriteLine("OracleDataAccessVersion: " + GetOracleDataAccessVersion());
            string connectionString = @"DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=###.###.###.###)(PORT=1521))(CONNECT_DATA=(SID=###)));USER ID=###;PASSWORD=###;PERSIST SECURITY INFO=False;Pooling=true;Max Pool Size=50;Min Pool Size=5;Incr Pool Size=3; Decr Pool Size=2;Connection Lifetime=30;Connection Timeout=60;";
            using (OracleConnection conn = new OracleConnection(connectionString)) {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.BindByName = true;
                cmd.Connection = conn;
                cmd.CommandText = "begin open :p_result for select * from all_types; end;";
                cmd.Parameters.Add("p_result", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
                int result = cmd.ExecuteNonQuery();
                using (OracleDataAdapter adapter = new OracleDataAdapter()) {
                    foreach (OracleParameter p in cmd.Parameters) {
                        if (p.OracleDbType == OracleDbType.RefCursor) {
                            var refCursor = p.Value as OracleRefCursor;
                            if (refCursor != null && !refCursor.IsNull) {
                                DataSet ds = new DataSet();
                                DataTable dt = new DataTable(p.ParameterName);
                                ds.Tables.Add(dt);
                                int records = 0;
                                int startRecord = 0;
                                int blockSize = 100;
                                Console.WriteLine("Begin read...");
                                try {
                                    do {
                                        records = adapter.Fill(ds, startRecord, blockSize, dt.TableName, refCursor);
                                        startRecord += records;
                                        Console.WriteLine("Got {0} records...", records);
                                    } while (records == blockSize);
                                    Console.WriteLine("Finished reading.");
                                    Console.WriteLine("Total rows: " + dt.Rows.Count);
                                } catch (Exception ex) {
                                    Console.WriteLine(ex.Message);
                                }
                            }
                        }
                    }
                }
            }            
            Console.WriteLine("Press any key...");
            Console.ReadKey();
        }
    }
}

I replaced some info in connection string with # for security reasons (same error happens on express and standard edition of Oracle 11g).

This code produces following output:

 

OracleDataAccessVersion: 4.121.1.0
Begin read...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
ORA-01001: invalid cursor
Press any key...

 

Code works fine when using old ODP.NET:

 

OracleDataAccessVersion: 2.111.6.20
Begin read...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 53 records...
Finished reading.
Total rows: 1353
Press any key...

 

The other thing I noticed is, if I replace CommandText with follwing ("select *" is replaced by "select type_name")

 

                cmd.CommandText = "begin open :p_result for select type_name from all_types; end;";

 

then it works with managed ODP.NET:

 

OracleDataAccessVersion: 4.121.1.0
Begin read...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 53 records...
Finished reading.
Total rows: 1353
Press any key...

 

This is just an example that produces this error, our code calls stored procedures and functions that returns RefCursors.

I also thought that this may be a problem with some column data types so I tried following select that contains only varchar columns:

 

                cmd.CommandText = "begin open :p_result for select OWNER, TYPE_NAME, TYPECODE, PREDEFINED, INCOMPLETE, FINAL from all_types; end;";

 

I get following output:

 

OracleDataAccessVersion: 4.121.1.0
Begin read...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
Got 100 records...
ORA-01001: invalid cursor
Press any key...

 

I don't know if there is a problem on my side or this is a problem with new managed odp.net.

I would really like to use this managed library in production environment.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points