If I have a record defined in a PL/SQL package and a procedure defined in the same package, is it possible to create the "record" type on the .NET (C#) side and pass it to the procedure using the type t_my_rec. I'm sure I can do this using UDTs (Oracle user-defined data types), but since I am using the managed driver, it isn't yet supported.
TYPE t_arr_my_rec IS TABLE OF t_my_rec INDEX BY PLS_INTEGER;
TYPE t_my_rec IS RECORD
(
item_id items.item_id%type,
item_name items.item_name%type
);
PROCEDURE insert_my_rec
(
p_my_rec in t_my_rec
);
PROCEDURE bulk_insert_my_rec
(
p_my_recs in t_arr_my_rec
);
Ideally I'd like to avoid defining array types for every single item in the table to do bulk FORALL insert statements.
I really appreciate the help!
I don't think you can deal with Oracle type declarations in ODP.net outside a UDT, and even then I've only done so with Type declarations made in the database rather than in a package.
You could also consider passing a collection of objects across in an XML object and parsing it out at both sides. That ensures that you can define the structures in play, although you will incur the overhead of creating / validating / parsing the string, and the data overhead of passing numbers as strings rather than as a couple of bytes.
Heck, in the old days before any decent UDT or XML support I remember stuffing a bunch of data into a CLOB to pass across and parse out, once both sides agreed on the format. Works OK if you never ever EVER expect to change the data object. A flipping maintenance nightmare otherwise. But do-able.