ODP.NET

Why does Oracle ManagedDataAccess (4.121.1.0) throw a "first chance" exception of System.FormatException to the debugger when using OracleCommand.BindByName (on some machines but not others)?

This happens when calling a ref-cursor-returning packaged stored function with OracleCommand.BindByName set to true.

When BindByName not set it works as expected.

I have only been able to recreate this issue on one machine in my control. This issue has also been reported to me by a customer.

I have tried changing the system locale settings from EN-UK to EN-US, but I wasn't aware of any change in behaviour.

Similarly I tried explicitly overriding the System Locale default with OracleGlobalization settings, and this also had no effect.

Please see the example below.

---

Environment (failing):

Windows 7 Professional 64-bit SP1

Visual Studio 12.0.21005.1 REL // MAY BE SIGNIFICANT

.Net Framework 4.5.51209

System Locale: EN-UK (but also fails with EN-US).

Oracle.ManagedDataAccess 4.121.1.0

<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1"/>

Environment (baseline):

Windows 7 Professional 64-bit SP1

Visual Studio 12.0.31101.00 Update 4 //SEE above

.Net Framework 4.5.51209

System Locale: EN-UK

Oracle.ManagedDataAccess 4.121.1.0

<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1"/>

Call stack:

>    ManagedSimpleRefCursor1.exe!NoddyConsole.Program.Command_ExecuteReader_BindByName_Fails() Line 63    C#

Example:

----

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Oracle.ManagedDataAccess.Client;

using Oracle.ManagedDataAccess.Types;

using System.Data;

namespace NoddyConsole

{

    public class Program

    {

        public static int Main(string[] args)

        {

            int errors = 0;

            errors += Command_ExecuteReader_BindByName_Fails();

            errors += UseReturnValue_Command_ExecuteNonQuery_BindByName_Fails();

            return errors;

        }

        public static int Command_ExecuteReader_BindByName_Fails()

        {

            int errors = 0;

            try

            {

                //var conn = new OracleConnection("User Id=USER;Password=PASS;Data Source=//your-host:1521/your-sid");

                var conn = new OracleConnection("User Id=UNIT620;Password=UNIT620;Data Source=//ln1apidb01:1521/fm");

                conn.Open();

                //NOT RELEVANT

                //OracleGlobalization og = conn.GetSessionInfo();

                //og.DateFormat = "DD-MON-YYYY HH24:MI:SS";

                //conn.SetSessionInfo(og);

                var cmd = conn.CreateCommand();

                cmd.CommandType = CommandType.Text;

                cmd.BindByName = true;

                cmd.CommandText = "BEGIN :rc := SHAUN.recreate_issue_1(in_code => :in_code); END;";

                var prm1 = cmd.CreateParameter();

                prm1.ParameterName = "rc";

                prm1.Direction = ParameterDirection.ReturnValue;

                prm1.OracleDbType = OracleDbType.RefCursor;

                cmd.Parameters.Add(prm1);

                /* SHAUN.recreate_issue_1 */

                var prm2 = cmd.CreateParameter();

                prm2.ParameterName = "in_code";

                prm2.Direction = ParameterDirection.Input;

                prm2.OracleDbType = OracleDbType.Varchar2;

                prm2.Value = "A";

                cmd.Parameters.Add(prm2);

                /* */

                var reader = cmd.ExecuteReader(); // FAILS at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

                /* *-/

                OracleRefCursor orc = null;

                cmd.ExecuteNonQuery(); // should fail here

                orc = cmd.Parameters["rc"].Value as OracleRefCursor;

                if (orc == null)

                    throw new InvalidOperationException("Invalid return type for ref cursor");

                var reader = orc.GetDataReader();

                /* */

                while (reader.Read())

                {

                    var values = new object[reader.FieldCount];

                    reader.GetOracleValues(values);

                    for (int i = 0; i < values.Length; ++i)

                    {

                        Console.WriteLine(values[i]);

                    }

                }

                reader.Dispose();

                cmd.Dispose();

                conn.Dispose();

            }

            catch (Exception ex)

            {

                Console.Error.WriteLine(ex.Message);

                errors++;

            }

            finally

            {

                Console.ReadKey(true);

            }

            return errors;

        }

        public static int UseReturnValue_Command_ExecuteNonQuery_BindByName_Fails()

        {

            int errors = 0;

            try

            {

                var conn = new OracleConnection("User Id=USER;Password=PASS;Data Source=//your-host:1521/your-sid");

                conn.Open();

                // NOT RELEVANT

                //OracleGlobalization og = conn.GetSessionInfo();

                //og.DateFormat = "DD-MON-YYYY HH24:MI:SS";

                //conn.SetSessionInfo(og);

                var cmd = conn.CreateCommand();

                cmd.BindByName = true; // ####

                cmd.CommandText = "BEGIN :rc := SHAUN.recreate_issue_1(in_code => :in_code); END;";

                //cmd.CommandText = "BEGIN :rc := SHAUN.recreate_issue_2; END;"; // no params, works

                var prm1 = cmd.CreateParameter();

                prm1.ParameterName = "rc";

                prm1.Direction = ParameterDirection.ReturnValue;

                prm1.OracleDbType = OracleDbType.RefCursor;

                cmd.Parameters.Add(prm1);

                /* SHAUN.recreate_issue_1 */

                var prm2 = cmd.CreateParameter();

                prm2.ParameterName = "in_code";

                prm2.Direction = ParameterDirection.Input;

                prm2.OracleDbType = OracleDbType.Varchar2;

                prm2.Value = "A";

                cmd.Parameters.Add(prm2);

                /* */

                //var reader = cmd.ExecuteReader();

                OracleRefCursor orc = null;

                // FAILS HERE when cmd.BindByName = true; First chance exception of System.FormatException in at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

                cmd.ExecuteNonQuery();

                orc = cmd.Parameters["rc"].Value as OracleRefCursor;

                if (orc == null)

                    throw new InvalidOperationException("Invalid return type for ref cursor");

                var reader = orc.GetDataReader();

                while (reader.Read())

                {

                    var values = new object[reader.FieldCount];

                    reader.GetOracleValues(values);

                    for (int i = 0; i < values.Length; ++i)

                    {

                        Console.WriteLine(values[i]);

                    }

                }

                reader.Dispose();

                orc.Dispose();

                cmd.Dispose();

                conn.Dispose();

            }

            catch (Exception ex)

            {

                Console.Error.WriteLine(ex.Message);

                errors++;

            }

            finally

            {

                Console.ReadKey(true);

            }

            return errors;

        }

    }

}

---

create or replace package SHAUN is

type ref_cur_type is ref cursor;

function recreate_issue_1(

           in_code varchar2 := null

) return ref_cur_type;

end SHAUN;

/

create or replace package body SHAUN is

--TTS000233

    function recreate_issue_1(

      in_code varchar2 := null

    ) return ref_cur_type is ref_cur ref_cur_type;

    begin

      open ref_cur for

        select UO.object_name from user_objects UO;

       return ref_cur;

end recreate_issue_1;

end SHAUN;

/

---