Hi Alex
This is related to (Original posting Jul 13, 2015 9:25AM) https://community.oracle.com/thread/3765109 .
This issue is not resolved.
We have had another customer complaining about this, as it disrupts their debugging workflow, and is a potential performance issue for them.
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.
Debugger configuration:
VS 2015 - "Just my code" unset, Exception Settings > Common Language Runtime Exceptions to "Break When Thrown".
Exception Stack:
mscorlib.dll!System.Number.StringToNumber(string str, System.Globalization.NumberStyles options, ref System.Number.NumberBuffer number, System.Globalization.NumberFormatInfo info, bool parseDecimal) Unknown
mscorlib.dll!System.Number.ParseInt32(string s, System.Globalization.NumberStyles style, System.Globalization.NumberFormatInfo info) Unknown
Oracle.ManagedDataAccess.dll!Oracle.ManagedDataAccess.Types.OracleRefCursor.OracleRefCursor(Oracle.ManagedDataAccess.Client.OracleConnection connection, OracleInternal.ServiceObjects.OracleRefCursorImpl refCursorImpl, Oracle.ManagedDataAccess.Types.OracleIntervalDS sessionTimeZone, string commandText, string paramPosOrName, long initialLongFS, long clientInitialLobFS, long internalInitialLOBFS, long[] scnFromExecution) Unknown
Oracle.ManagedDataAccess.dll!OracleInternal.ServiceObjects.OracleParameterImpl.ExtractRefCursorFromAccessor(Oracle.ManagedDataAccess.Client.OracleConnection conn, OracleInternal.TTC.Accessors.Accessor accessor, long fetchSize, Oracle.ManagedDataAccess.Client.PrmEnumType enumType, Oracle.ManagedDataAccess.Types.OracleIntervalDS sessionTimeZone, string commandText, string paramPosOrName, long longFetchSize, long clientInitialLOBFS, long internalInitialLOBFS, long[] scnFromExecution, int currentRow, bool bCallFromExecuteReader) Unknown
Oracle.ManagedDataAccess.dll!OracleInternal.ServiceObjects.OracleParameterImpl.GetRefCursorFromBytes(Oracle.ManagedDataAccess.Client.OracleConnection conn, OracleInternal.TTC.Accessors.Accessor accessor, long fetchSize, Oracle.ManagedDataAccess.Client.PrmEnumType enumType, Oracle.ManagedDataAccess.Types.OracleIntervalDS sessionTimeZone, string commandText, string paramPosOrName, long longFetchSize, long clientInitialLOBFS, long internalInitialLOBFS, long[] scnFromExecution, bool bCallFromExecuteReader) Unknown
Oracle.ManagedDataAccess.dll!Oracle.ManagedDataAccess.Client.OracleParameter.PostBind_RefCursor(Oracle.ManagedDataAccess.Client.OracleConnection connection, OracleInternal.TTC.Accessors.Accessor bindAccessor, long fetchSize, Oracle.ManagedDataAccess.Types.OracleIntervalDS sessionTimeZone, string commandText, string paramPosOrName, long longFetchSize, long clientInitialLOBFS, long internalInitialLOBFS, long[] scnFromExecution, bool bCallFromExecuteReader) Unknown
Oracle.ManagedDataAccess.dll!OracleInternal.ServiceObjects.OracleCommandImpl.ExtractAccessorValuesIntoParam(Oracle.ManagedDataAccess.Client.OracleParameterCollection paramColl, Oracle.ManagedDataAccess.Client.OracleConnection connection, string commandText, long longFetchSize, long clientInitialLOBFS, long internalInitialLOBFS, long[] scnFromExecution, bool bCallFromExecuteReader) Unknown
Oracle.ManagedDataAccess.dll!Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(bool requery, bool fillRequest, System.Data.CommandBehavior behavior) Unknown
Oracle.ManagedDataAccess.dll!Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader() Unknown
EXA000071-2-Console.exe!EXA000071_2_Console.Program.Case1_Command_ExecuteReader_BindByName_Fails() Line 71 C#
EXA000071-2-Console.exe!EXA000071_2_Console.Program.Main() Line 14 C#
----
Environment
OS Name Microsoft Windows 10 Enterprise
Version 10.0.10586 Build 10586
.NET Framework 4.6.1 (394254)
User Locale
Display EN-UK
Input EN-UK
Format EN-UK
Location UK
Oracle ManagedDataAccess Provider
<package id="Oracle.ManagedDataAccess" version="12.1.24160719" targetFramework="net461" />
Oracle Target
NLS_SESSION_PARAMETERS:
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_CURRENCY £
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_SORT BINARY
NLS_TIME_FORMAT HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY ?
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
NLS_INSTANCE_PARAMETERS:
NLS_LANGUAGE ENGLISH
NLS_TERRITORY UNITED KINGDOM
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
NLS_DATABASE_PARAMETERS:
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8MSWIN1252
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
-- NB: this occurs in other configurations.
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");
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 see callstack
/* *-/
OracleRefCursor orc = null;
cmd.ExecuteNonQuery(); // FAILS see callstack
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; see call stack.
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;
}
}
}
PL-SQL Code
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
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;
/
Would it be possible to look into this further?
Many thanks
Shaun McGrath
Message was edited by: mcgraths: Added example code that was initially omitted from the question.