使用recordtype输入参数执行oracle包会在.NET中引发异常 [英] Executing oracle package with recordtype input param throws exception in .NET
问题描述
嗨.Net Gurus，
过去几天我遇到了问题。问题详述如下。
有15个输入参数（类型为int和/ varchar），这个oracle包的1个记录类型参数和5个输出参数。
有人观察到，当将记录类型参数传递给包时抛出此异常，对那些没有记录的包工作正常类型参数。
异常抛出： - 无法将System.Int32类型的对象强制转换为System.Array。
堆栈跟踪： - 在Oracle.DataAccess.Client.OracleParameter.SetStatus（Int32 arraySize）
at Oracle.DataAccess.Client.OracleParameter.ResetCtx （Int32 arraySize）
at Oracle.DataAccess.Client.OracleParameter.PreBind（OracleConnection conn，IntPtr errCtx，Int32 arraySize）
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery （）
在POC_App.DataformatConverter.ExecuteCreateSOPackage（）
我尝试过：
public static void ExecuteCreateSOPackage（）
{
string constr = EstablishDBConnection（）;
OracleConnection con = new OracleConnection（constr）;
con.Open（）;
OracleCommand cmd = new OracleCommand（APPS.XX_OM_CREATE_SO_TEST.create_sale_orders，con）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.ArrayBindCount = 100;
OracleParameter retVal = new OracleParameter（retVal，OracleDbType.RefCursor）;
retVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add（retVal）;
OracleParameter paramPersonId = setInputParameters（ p_person_id，OracleDbType.Int32,0）;
paramPerson Id.Value = 31842;
cmd.Parameters.Add（paramPersonId）;
（同样还有14个输入参数）
---------------------------------
----- -------------------------------
//记录类型参数..
OracleParameter p_so_lines_tab = new OracleParameter（）;
p_so_lines_tab.ParameterName =p_so_lines_tab;
p_so_lines_tab.OracleDbType = OracleDbType。 NVarchar2;
p_so_lines_tab.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_so_lines_tab.Size = 3;
p_so_lines_tab.Direction = ParameterDirection.Input;
OracleParameter P_SO_LINES_TAB_ITEM = new OracleParameter（）;
P_SO_LINES_TAB_ITEM.ParameterName =p_so_lines_tab_item;
P_SO_LINES_TAB_ITEM.OracleDbType = OracleDbType.NVarchar2;
P_SO_LINES_TAB_ITEM.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
P_SO_LINES_TAB_ITEM.Size = 1000;
P_SO_LINES_TAB_ITEM.Direction = ParameterDirection.Input;
OracleParameter QUANTITY =新的OracleParameter（）;
QUANTITY.ParameterName =quantity;
QUANTITY。 OracleDbType = OracleDbType.Int32;
QUANTITY.Value = 1;
OracleParameter QUANTITY_UOM =新的OracleParameter（）;
QUANTITY_UOM.ParameterName =quantity_uom;
QUANTITY_UOM.OracleDbType = OracleDbType.Varchar2;
QUANTITY_UOM.Value =Ea;
OracleParameter ITEM_ID = new OracleParameter（）;
ITEM_ID.ParameterName =item_id;
ITEM_ID.OracleDbType = OracleDbType.Int32;
ITEM_ID.Value = 162744;
OracleParameter PROMISE_DATE = new OracleParameter（）;
PROMISE_DATE.ParameterName = promise_date;
PROMISE_DATE.OracleDbType = OracleDbType.Date;
PROMISE_DATE.Value =2016-04-01T08：58：50.649Z;
OracleParameter SCHEDULE_SHIP_DATE = new OracleParameter（）;
SCHEDULE_SHIP_DATE.ParameterName =schedule_ship_date;
SCHEDULE_SHIP_DATE.OracleDbType = OracleDbType.Date;
SCHEDULE_SHIP_DATE.Value =2016-04-01T08：58：50.649Z;
OracleParameter CURRENCY_CODE = new OracleParameter（）;
CURRENCY_CODE.ParameterName =currency_code;
CURRENCY_CODE.OracleDbType = OracleDbType.Varchar2;
CURRENCY_CODE.Value =USD;
OracleParameter UNIT_SELLING_PRICE = new OracleParameter（）;
UNIT_SELLING_PRICE.ParameterName =unit_selling_price;
UNIT_SELLING_PRICE.OracleDbType = OracleDbType .Int32;
UNIT_SELLING_PRICE.Value = 200;
OracleParameter SALESREP_ID = new OracleParameter（）;
SALESREP_ID.ParameterName =salesrep_id;
SALESREP_ID.OracleDbType = OracleDbType.Int32;
SALESREP_ID.Value = 1412;
P_SO_LINES_TAB_ITEM.Value =新对象[] {QUANTITY，QUANTITY_UOM ，ITEM_ID，PROMISE_DATE，SCHEDULE_SHIP_DATE，CURRENCY_CODE，UNIT_SELLING_PRICE，SALESREP_ID};
//添加到最外层的集合
p_so_lines_tab.Value = P_SO_LINES_TAB_ITEM;
cmd.Parameters.Add（p_so_lines_tab）;
cmd.Parameters.Add（ x_time_stamp，OracleDbType.TimeStamp，ParameterDirection.Output）;
cmd.Parameters.Add（x_order_header_id，OracleDbType.Int32，ParameterDirection.Output）;
cmd.Parameters.Add（x_order_number，OracleDbType.Int32，ParameterDirection.Output）;
cmd。 Parameters.Add（x_return_code，OracleDbType.Varchar2，ParameterDirection.Output）;
cmd.Parameters.Add（x_return_msg，OracleDbType.Varchar2，ParameterDirection.Output）;
尝试
{
cmd.ExecuteNonQuery（）;
}
catch（Exception exp）
{
string str = exp.StackTrace.ToString（）;
string msg = exp.Message.ToString（）;
}
}
Hi .Net Gurus,
I am stuck on an issue for past few days.The issue is detailed below.
There are 15 input params(of type int and varchar),1 record type param and 5 output params for this oracle package.
It was observed that while passing record type parameter to the package this exception is thrown,work fine with those packages with out record type param.
Exception Thrown:-Unable to cast object of type 'System.Int32' to type 'System.Array'.
Stack Trace:- at Oracle.DataAccess.Client.OracleParameter.SetStatus(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.ResetCtx(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at POC_App.DataformatConverter.ExecuteCreateSOPackage()
What I have tried:
public static void ExecuteCreateSOPackage()
{
string constr = EstablishDBConnection();
OracleConnection con = new OracleConnection(constr);
con.Open();
OracleCommand cmd = new OracleCommand("APPS.XX_OM_CREATE_SO_TEST.create_sale_orders", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.ArrayBindCount = 100;
OracleParameter retVal = new OracleParameter("retVal", OracleDbType.RefCursor);
retVal.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(retVal);
OracleParameter paramPersonId = setInputParameters("p_person_id", OracleDbType.Int32, 0);
paramPersonId.Value = 31842;
cmd.Parameters.Add(paramPersonId);
(likewise 14 more input params )
---------------------------------
------------------------------------
//Record type parameter..
OracleParameter p_so_lines_tab = new OracleParameter();
p_so_lines_tab.ParameterName = "p_so_lines_tab";
p_so_lines_tab.OracleDbType = OracleDbType.NVarchar2;
p_so_lines_tab.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p_so_lines_tab.Size = 3;
p_so_lines_tab.Direction = ParameterDirection.Input;
OracleParameter P_SO_LINES_TAB_ITEM = new OracleParameter();
P_SO_LINES_TAB_ITEM.ParameterName = "p_so_lines_tab_item";
P_SO_LINES_TAB_ITEM.OracleDbType = OracleDbType.NVarchar2;
P_SO_LINES_TAB_ITEM.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
P_SO_LINES_TAB_ITEM.Size = 1000;
P_SO_LINES_TAB_ITEM.Direction = ParameterDirection.Input;
OracleParameter QUANTITY = new OracleParameter();
QUANTITY.ParameterName = "quantity";
QUANTITY.OracleDbType = OracleDbType.Int32;
QUANTITY.Value = 1;
OracleParameter QUANTITY_UOM = new OracleParameter();
QUANTITY_UOM.ParameterName = "quantity_uom";
QUANTITY_UOM.OracleDbType = OracleDbType.Varchar2;
QUANTITY_UOM.Value = "Ea";
OracleParameter ITEM_ID = new OracleParameter();
ITEM_ID.ParameterName = "item_id";
ITEM_ID.OracleDbType = OracleDbType.Int32;
ITEM_ID.Value = 162744;
OracleParameter PROMISE_DATE = new OracleParameter();
PROMISE_DATE.ParameterName = "promise_date";
PROMISE_DATE.OracleDbType = OracleDbType.Date;
PROMISE_DATE.Value = "2016-04-01T08:58:50.649Z";
OracleParameter SCHEDULE_SHIP_DATE = new OracleParameter();
SCHEDULE_SHIP_DATE.ParameterName = "schedule_ship_date";
SCHEDULE_SHIP_DATE.OracleDbType = OracleDbType.Date;
SCHEDULE_SHIP_DATE.Value = "2016-04-01T08:58:50.649Z";
OracleParameter CURRENCY_CODE = new OracleParameter();
CURRENCY_CODE.ParameterName = "currency_code";
CURRENCY_CODE.OracleDbType = OracleDbType.Varchar2;
CURRENCY_CODE.Value = "USD";
OracleParameter UNIT_SELLING_PRICE = new OracleParameter();
UNIT_SELLING_PRICE.ParameterName = "unit_selling_price";
UNIT_SELLING_PRICE.OracleDbType = OracleDbType.Int32;
UNIT_SELLING_PRICE.Value = 200;
OracleParameter SALESREP_ID = new OracleParameter();
SALESREP_ID.ParameterName = "salesrep_id";
SALESREP_ID.OracleDbType = OracleDbType.Int32;
SALESREP_ID.Value = 1412;
P_SO_LINES_TAB_ITEM.Value = new object[] { QUANTITY, QUANTITY_UOM, ITEM_ID, PROMISE_DATE, SCHEDULE_SHIP_DATE, CURRENCY_CODE, UNIT_SELLING_PRICE, SALESREP_ID };
//Adding the to outer most collection
p_so_lines_tab.Value = P_SO_LINES_TAB_ITEM;
cmd.Parameters.Add(p_so_lines_tab);
cmd.Parameters.Add("x_time_stamp", OracleDbType.TimeStamp, ParameterDirection.Output);
cmd.Parameters.Add("x_order_header_id", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("x_order_number", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add("x_return_code", OracleDbType.Varchar2, ParameterDirection.Output);
cmd.Parameters.Add("x_return_msg", OracleDbType.Varchar2, ParameterDirection.Output);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.StackTrace.ToString();
string msg = exp.Message.ToString();
}
}
推荐答案
为上述问题找到解决方案。
Got a solution for the above mentioned problem.
var commandObject = new OracleCommand
{
Connection = con，
CommandText =你的包名，
CommandType = CommandType.StoredProcedure
};
commandObje ct.Connection.Open（）;
commandObject.Parameters.Add（param1，OracleDbType.Int32，ParameterDirection.Input）.Value = some int value;
commandObject.Parameters.Add（param2 ，OracleDbType.Int32，ParameterDirection.Input）.Value = some int value;
commandObject.Parameters.Add（param3，OracleDbType.Int32，ParameterDirection.Input）.Value = some int value;
commandObject.Parameters.Add（p_xml_type，OracleDbType.NVarchar2，ParameterDirection.Input）.Value =某个集合
commandObject.Parameters.Add（param returncode，OracleDbType .Varchar2,200）。Direction =some string value;
commandObject.ExecuteNonQuery（）;
正在设置的集合在类型为字符串的xml格式，在oracle包中，参数定义如下所示。
var commandObject = new OracleCommand
{
Connection = con,
CommandText = "Your Package Name",
CommandType = CommandType.StoredProcedure
};
commandObject.Connection.Open();
commandObject.Parameters.Add("param1", OracleDbType.Int32, ParameterDirection.Input).Value = some int value;
commandObject.Parameters.Add("param2", OracleDbType.Int32, ParameterDirection.Input).Value = some int value;
commandObject.Parameters.Add("param3", OracleDbType.Int32, ParameterDirection.Input).Value = some int value;
commandObject.Parameters.Add("p_xml_type", OracleDbType.NVarchar2, ParameterDirection.Input).Value =some collection
commandObject.Parameters.Add("param returncode", OracleDbType.Varchar2, 200).Direction = "some string value";
commandObject.ExecuteNonQuery();
The collection being set is in xml format of type string and in the oracle package the parameter has been defined as shown below.
p_xml_type IN CLOB
这篇关于使用recordtype输入参数执行oracle包会在.NET中引发异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
