Contents tagged with ODP.NET

  • ODP.NET Associative Arrays

    Through ODP.NET you can use Oracle's Associative Array feature. Unfortunately, there is a limitation: you cannot have associative arrays with an index type other that number.

    If you have this stored procedure:

    PACKAGE
    MyPackage AS

    TYPE AssocArrayVarchar2_t IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

    PROCEDURE MyStoredProcedure

    (

        Param1 IN AssocArrayVarchar2_t

    );

    END MyPackage;

    You can use this code to call it:

    using (OracleConnection con = new OracleConnection(@"DATA SOURCE=(DESCRIPTION=(ADDRESS=(COMMUNITY=MyCommunity)(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SID=MySid)));USER ID=MyUsername;PASSWORD=MyPassword;Promotable Transaction=local"))using (OracleCommand cmd = con.CreateCommand())

    {

        con.Open();

        cmd.CommandText = @"MyPackage.MyStoredProcedure";

        cmd.CommandType = CommandType.StoredProcedure;   

        OracleParameter param1 = cmd.Parameters.Add("param1", OracleDbType.Varchar2, ParameterDirection.Input);

        param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

        param1.Value = new String[] { "First Element", "Second Element", "Third Element" };

        cmd.ExecuteNonQuery();

    }

    Read more...