Analytics Software

TYPE OutParamArray IS TABLE OF VARCHAR2(200) index by binary_integer;

/*

create or replace package mypack5 as
TYPE v2array is table of varchar2(4000) index by binary_integer;
PROCEDURE test_it(thearray out v2array);
END;
/
CREATE or replace PACKAGE BODY MYPACK5 AS
PROCEDURE test_it(thearray out v2array) IS
 begin
    thearray(1):='hello';
    thearray(2):='world'; 
    thearray(3):='from the stored procedure';
 END; 
END;
/
*/

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

public class out_v2_tab
{
    public static void Main()
    {

        using (OracleConnection con = new OracleConnection("data source=orcl;user id=scott;password=tiger;"))
        {
            con.Open();

            using (OracleCommand cmd = new OracleCommand("mypack5.test_it", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter Param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2);
                Param1.Direction = ParameterDirection.Output;
                Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                Param1.Size = 3;
                Param1.ArrayBindSize = new int[]{4000,4000,4000};

                cmd.ExecuteNonQuery();
                for (int i = 0; i < 3; i++)
                    Console.WriteLine("Param1[{0}] = {1} ", i,
                      (cmd.Parameters[0].Value as Array).GetValue(i));
            }
        }
    }
}