C# ODP.NET 如何将字符串数组传递给 Oracle 存储过程？

ODP.NET How to pass array of strings to an Oracle Stored procedure?

提问by Vinzz

There's numerous question and confusingdocson the subject, but no luck so far.

关于这个主题有很多问题和令人困惑的文档，但到目前为止还没有运气。

I've the following PL/SQL stored procedure;

我有以下 PL/SQL 存储过程；

PROCEDURE PS_test(
  Liste1 Listcar,
  Liste2 Listcar,
  P_CURS_MESSAGE out CURSOR_REF_TYP
)

Where the type Listcaris the following:

其中类型Listcar如下：

TYPE Listcar IS VARRAY(100) OF VARCHAR2(50);

类型 Listcar 是 VARRAY(100) OF VARCHAR2(50);

Here is what I'm trying so far:

这是我目前正在尝试的：

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);

With the following error on the Value assignment:

值分配出现以下错误：

Value does not fall within the expected range.

Tried to use the type varchr2, to set the ArrayBindSize and so on, but no luck so far.

尝试使用类型 varchr2，设置 ArrayBindSize 等，但到目前为止没有运气。

I guess the interface IOracleArrayTypeFactorymight play a role somewhere, but how?

我猜界面IOracleArrayTypeFactory可能在某处发挥作用，但是如何呢？

采纳答案by Yin

I haven't used the udtType feature in ODP.NET, so I am not sure how to achieve your goal with this. However, to pass an array of string you don't need it.

我没有在 ODP.NET 中使用过 udtType 功能，所以我不确定如何用它来实现你的目标。但是，要传递一个字符串数组，您不需要它。

Like the documentation you attached, you need to create a package contains your stored procedure, and takes an associative array (not VARRAY) as input parameter.

与您附加的文档一样，您需要创建一个包含您的存储过程的包，并将关联数组（不是 VARRAY）作为输入参数。

For example:

例如：

-- 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;
/

Now, run this code, put your own connection string.

现在，运行此代码，放置您自己的连接字符串。

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
    }
}