CODEWITHSUNDEEP
c#stored-proceduresoracle11gpackagevarray
Leigh Riffel
Leigh Riffel

Reputation: 6641

C# call to Oracle 11g Procedure with VARRAY parameter

How from ASP.NET I can call a stored procedure in an Oracle package that accepts a VArray. The VArray will pass a list of keys that the stored procedure will use to modify appropriate records. I know I could send the keys in separate calls or send a delimited list, but I'd rather use an array.

Upvotes: 2

Views: 6583

Answers (2)

Vadim K.
Vadim K.

Reputation: 2436

Assuming you're using ODP.NET (you shouldn't be using System.Data.OracleClient anyway), here's how to do it:

using System;
using System.Data;
using Oracle.DataAccess.Client;

class SomeClass
{
    void SomeMethod(string connectionString, int[] anArrayOfKeys)
    {
        using (var con = new OracleConnection(connectionString))
        using (var cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "StoredProcedureNameGoesHere";
            cmd.Parameters.Add(
                "ParameterNameGoesHere",
                OracleDbType.Array,
                anArrayOfKeys,
                ParameterDirection.Input);

            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Upvotes: 2

Craig
Craig

Reputation: 36836

Did you try this

OracleCommand cmd = OracleConnection1.CreateCommand();
cmd.CommandText = "INSERT INTO ArrayTable VALUES (:CODE, :TITLE, : ARR1, :ARR2)";
...
OracleArray arr1 = new OracleArray("SCOTT.TARRAY1", OracleConnection1);
arr1.Add(10);
arr1.Add(20);
arr1.Add(30);
...
cmd.Parameters["ARR1"].DbType = OracleDbType.Array;
cmd.Parameters["ARR1"].Value = arr1;
...
cmd.ExecuteNonQuery();

Upvotes: -1

Related Questions