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.
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.
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
}
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…