This discussion is archived
1 Reply Latest reply: Dec 13, 2012 4:14 AM by Johnsung RSS

Bug: in ManagedDataAccess while fetch from "Select .., NULL, ... From dual"

user540519 Newbie
Currently Being Moderated
Hi,
I have the problem with the new managed ODP.Net driver.
If I fetch data from a select which has column with a fixed value "NULL" the driver decides after a certain amount of data that the value is not null any more. Which causes a lot of problems especially if you try to hide not needed blob data.
The Problem somehow depends on the FetchSize of the command. It seems like the error occurs if more than one db round trip to fetch the result is necessary.


System: Windows 7 64 Bit
Platform: .net 4.0 x86
database: 11g Release 11.2.0.3.0 - 64bit Production
Oracle.ManagedDataAccess Version: 4.112.350


I created a small example to reproduce the problem.

Thanks for your help
Dominik

Stored Proc:
create or replace PROCEDURE TestNullField
(
  v_IntPara IN NUMBER DEFAULT NULL ,
  v_StrPara IN VARCHAR2 DEFAULT NULL,
  cv_1 OUT SYS_REFCURSOR
)
AS

BEGIN

      OPEN cv_1 FOR
        select rownum, v_StrPara, NULL from dual connect by level <= v_IntPara;
        --select IDX, NULL, DESCRIPTION FROM TEST_BLOBTABLE;
END;
C# Code:
using System;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace OracleBlobTest
{
    class Program
    {
        private static string _connectionString = @"User ID=YourUser;Password=YourPwd;Data Source=YourServer:YourPort/YourSchema;";
        private static string _spName = @"TestNullField";
        private static string _strPara = @" Long test string";
        private static int _intPara = 200;

        static void Main(string[] args)
        {
            using (OracleConnection connection = new OracleConnection(_connectionString))
            {
                using (OracleCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = _spName;
                    cmd.CommandType = CommandType.StoredProcedure;

                    connection.Open();

                    string alongString = _strPara;

                    while (alongString.Length < 2000)
                    {
                        alongString += alongString;
                    }
                    alongString = alongString.Substring(0, 2000);


                    OracleCommandBuilder.DeriveParameters(cmd);

                    if (cmd.Parameters.Count > 0 && (cmd.Parameters[0]).Direction != ParameterDirection.ReturnValue)
                    {
                        cmd.Parameters[0].Value = _intPara;
                        cmd.Parameters[1].Value = alongString;
                    }

                    // change this to change the moment when it starts to go wrong
                    ///cmd.FetchSize = 5000;

                    using (OracleDataReader reader = cmd.ExecuteReader())
                    {
                        int count = 0;
                        while (reader.Read())
                        {
                            count++;

                            for (int idx = 0; idx < reader.FieldCount; idx++)
                            {
                                if (reader.GetName(idx) == "NULL")
                                {
                                    if (!reader.IsDBNull(idx))
                                    {
                                        //something is very wrong here - why is not not null any more???
                                        Console.WriteLine("Fix NULL Field[{0}] {1} is not null >{2}< in row {3} ", idx, reader.GetName(idx), reader[idx], count);
                                    }
                                }
                            }
                        }

                        Console.WriteLine("Rows found: " + count);
                    }
                }
                connection.Close();
            }

            Console.WriteLine("done press enter");
            Console.ReadLine();
        }
    }
}
Edited by: user540519 on 10.12.2012 15:11

Edited by: user540519 on 19.12.2012 13:50
  • 1. Re: Oracle.ManagedDataAccess Bug Select NULL
    Johnsung Pro
    Currently Being Moderated
    Hello

    I ran the testcase here and reproduced the issue on 32 bit unmanaged beta v4.0.30319

    note: same testcase works with Oracle.DataAccess (but not with managed).

    This appears to match unpublished defect Bug 14666093 and is meant to be fixed in a later beta release.

    Some things I noticed when testing
    with the default fetchsize the breaking point is 67 iterations.
    e.g.
    private static int _intPara = 66;    // Works

    private static int _intPara = 67;  // Fails

    If I increase the fetchsize then it breaks at different values as you noticed..

    Hope this helps.

    Kind Regards
    John

Legend

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