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

问题描述

有很多问题，并且令人困惑 docs ，但到目前为止没有运气

There's numerous question and confusing docs on the subject, but no luck so far.

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

I've the following PL/SQL stored procedure;

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

其中类型Listcar如下:

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

TYPE Listcar IS 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.

试图使用类型varchr2来设置ArrayBindSize等等，但是到目前为止还没有运气.

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

我猜想接口IOracleArrayTypeFactory可能在某处起作用，但是如何?

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

推荐答案

我没有在ODP.NET中使用udtType功能，所以我不确定如何实现此目标.但是，不需要传递字符串数组.

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.

与随附的文档一样，您需要创建一个包含存储过程的程序包，并使用关联数组(不是VARRAY)作为输入参数.

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.

例如:

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

这篇关于ODP.NET如何将字符串数组传递给Oracle存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！