Call Oracle Storage Procedure with a record as parameter

Question

Hi everyone, is it possible to call an oracle storage procedure with a record type as IN parameter.

In Oracle I have a record definition:

TYPE R_InData_tab IS RECORD ( ... ); TYPE InData_tab IS TABLE OF R_InData_tab INDEX BY BINARY_INTEGER;

Now I want to set this record type as parameter:

PROCEDURE myProcedure (inRecord IN myPackage.InData_tab);

And call this procedure from my C# Code. Does anyone has an idea?

Thx

Answer 1

I think you are limited to the built-in Oracle types defined in OracleType. If so you would have to write a stored procedure that takes regular parameters, constructs the type value and calls the original procedure.

Answer 2

You can do this by sending a code block as the statement (it's been a while since I worked with Oracle so the syntax might be slightly off:

DECLARE
    param indata_tab;
BEGIN
    FOR i IN 1 .. :field1%COUNT LOOP
         param(i).field1 := :field1(i);
         param(i).field2 := :field2(i);
    END LOOP;
    myProcedure(param);
END;

and then you bind the field1 and field2 parameters to arrays.

Answer 3

If you're using Oracle Data Provider for .NET(ODP.NET), it's definitely possible to call the procedure directly without using the massaging suggested by erikkallen.

However, I'm not sure that will solve your problem. It looks like you're defining the types in the package that contains the procedure. In order to use the method linked above, your types need to be created as separate objects in the database, using DDL like so:

CREATE TYPE R_InData_tab AS OBJECT ( ... );
CREATE TYPE InData_tab AS TABLE OF R_InData_tab;

This may require some slight changes in the package, as INDEX BY types are not supported as schema objects, so nested tables (or varrays) will need to be used.