ODP.NET如何将字符串数组传递给Oracle存储过程?
问题描述:
关于这个问题有很多问题和confusingdocs,但迄今为止没有运气。ODP.NET如何将字符串数组传递给Oracle存储过程?
我有以下的PL/SQL存储过程;
PROCEDURE PS_test(
Liste1 Listcar,
Liste2 Listcar,
P_CURS_MESSAGE out CURSOR_REF_TYP
)
凡类型Listcar
如下:
TYPE Listcar IS VARRAY(100)VARCHAR2(50);
这里是我想到目前为止:
string[] list = { "name1", "name1" };
OracleParameter oParam = (OracleParameter)myOracleCommand.CreateParameter();
oParam.ParameterName = "Liste1";
oParam.UdtTypeName = "LISTCAR";
oParam.Value = list;
oParam.Direction = ParameterDirection.Input;
myOracleCommand.Parameters.Add(oParam);
随着对价值分配以下错误:
Value does not fall within the expected range.
试图利用类型varchr2,设置ArrayBindSize等目前为止,但没有运气。
我猜接口IOracleArrayTypeFactory
可能在某处发挥作用,但是怎么做?
答
我没有在ODP.NET中使用udtType功能,所以我不知道如何实现你的目标。但是,要传递一个字符串数组,你不需要它。
与您附加的文档一样,您需要创建一个包含存储过程的包,并将关联数组(不是VARRAY)作为输入参数。
例如:
-- Create the table
CREATE TABLE TBLTEST (testID NUMBER, name VARCHAR2(50));
CREATE SEQUENCE seq_test
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;
CREATE OR REPLACE PACKAGE pkgTestArrayBinding
AS
-- Define an local scope associative array type called T_ASSOCIATIVE_ARRAY and make it as the type of input parameter
TYPE T_ASSOCIATIVE_ARRAY IS TABLE OF VARCHAR(50) INDEX BY PLS_INTEGER;
PROCEDURE TestArrayBinding(
Param1 IN T_ASSOCIATIVE_ARRAY,
Param2 IN T_ASSOCIATIVE_ARRAY);
END pkgTestArrayBinding;
/
CREATE OR REPLACE PACKAGE BODY pkgTestArrayBinding
AS
PROCEDURE TestArrayBinding(
Param1 IN T_ASSOCIATIVE_ARRAY,
Param2 IN T_ASSOCIATIVE_ARRAY)
AS
BEGIN
-- for all loop to insert them in a batch
FORALL indx IN 1..Param1.COUNT
INSERT INTO tblTest VALUES(seq_test.nextval, Param1(indx));
FORALL indx IN 1..Param2.COUNT
INSERT INTO tblTest VALUES(seq_test.nextval, Param2(indx));
END TestArrayBinding;
END pkgTestArrayBinding;
/
现在,运行这段代码,把你自己的连接字符串。
namespace Con1
{
using System;
using System.Data;
using Oracle.DataAccess.Client;
/// <summary>
/// The program.
/// </summary>
internal class Program
{
#region Methods
/// <summary>
/// The main.
/// </summary>
private static void Main()
{
var con = new OracleConnection { ConnectionString = "User Id=usr;Password=pass;Data Source=XE" };
con.Open();
Console.WriteLine("Connected to Oracle" + con.ServerVersion);
// create command to run your package
var cmd = new OracleCommand("BEGIN pkgTestArrayBinding.TestArrayBinding(:Param1, :Param2); END;", con);
var param1 = cmd.Parameters.Add("Param1", OracleDbType.Varchar2);
var param2 = cmd.Parameters.Add("Param2", OracleDbType.Varchar2);
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Input;
// Specify that we are binding PL/SQL Associative Array
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
// Setup the values for PL/SQL Associative Array
param1.Value = new[] { "First Element", "Second Element ", "Third Element_" };
param2.Value = new[] { "Fourth Element", "Fifth Element ", "Sixth Element " };
// Specify the maximum number of elements in the PL/SQL Associative Array
// this should be your array size of your parameter Value.
param1.Size = 3;
param2.Size = 3;
// Setup the ArrayBindSize for each elment in the array,
// it should be bigger than the original length of element to avoid truncation
param1.ArrayBindSize = new[] { 13, 14, 13 };
// Setup the ArrayBindSize for Param2
param2.ArrayBindSize = new[] { 20, 20, 20 };
// execute the cmd
cmd.ExecuteNonQuery();
// I am lazy to query the database table here, but you should get you data now.
// watch what happened to element "Third Element_"
// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
Console.WriteLine("Disconnected");
}
#endregion
}
}
对于性能,关联数组优于VARRAY。以下是包含关联数组的一些与性能相关的演练:http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/OptimizePerfODPNET/OptimizePerfODPNET.htm由于VARRAY是用户定义类型,因此如果您仍想使用它们在这里是一个步骤,展示了如何使用.NET中的UDT一般http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/userdefinedtypes/userDefinedTypes.htm – 2012-03-16 08:01:10