Oracle: Passing Associative Array To Nested Table For Use in SQL Statement from C#

Question

I'm attempting to pass an array of numbers to an oracle stored procedure so I can process in bulk. I have to pass the associative array in and then populate a nested table with the contents. Is there a better way to pass an array of numbers or varchar2 to a stored procedure in oracle? Am I doing this right? :)

I've looked at temp tables and pipeline functions too as alternatives to this working method below.

create or replace type t_number_table as table of number;

create or replace package numberarray_pkg
as
    type t_numbers is table of number index by pls_integer;
    type t_cursor is ref cursor;

    procedure passarray(
      paymentids in t_numbers,
      io_cursor in out t_cursor);

end numberarray_pkg;
/

create or replace package body numberarray_pkg
as
    procedure passarray(paymentids in t_numbers, io_cursor in out t_cursor) as
      v_number_table t_number_table;
      v_cursor       t_cursor;
    begin
      v_number_table := t_number_table();
      v_number_table.extend(paymentids.count);

      for i in 1 .. paymentids.count loop
        v_number_table(i) := paymentids(i);
      end loop;

      open v_cursor for
        select column_value from table(v_number_table);

      io_cursor := v_cursor;
    end passarray;
end numberarray_pkg;
/

I'm populating the array in C# and reading the collection from a flat file. The only way I could pass the array of values to the stored procedure was by using the oracle associative array. new OracleParameter { ParameterName = "payment_ids", OracleDbType = OracleDbType.Int32, CollectionType = OracleCollectionType.PLSQLAssociativeArray, Value = array, Size = array.Length } Thanks for looking at my question, Alex. — Jonathan F
I see, I hadn't realised you couldn't use SQL table types like you can from Java. You should probably add that info to the question though, and tag it as C#. — Alex Poole

Alberto Annunziata Alberto Annunziata · Accepted Answer · 2016-03-24T12:17:10

In the script of body creation you must declare a default variable like this:

 type t_numbers is table of number index by pls_integer;
 nullNumber t_numbers;

In the specification of the procedure:

 procedure passarray(
      paymentids in t_numbers default nullNumber,
      io_cursor in out t_cursor);

And in code you must pass the collection as an array (important) only if the array has at least one value.

Oracle: Passing Associative Array To Nested Table For Use in SQL Statement from C#

1 Answers