CODEWITHSUNDEEP
c#oracle-databasestored-proceduresdata-access-layerodp.net
GeorgesC
GeorgesC

Reputation: 145

C# Call Oracle Stored Procedure with nested UDT

Is it possible to call a stored procedure with an nested object as a parameter?

For example I have a Person class that has an Address instance.

Person p1   = new Person();
p1.Name     = "John";
p1.Address  = new Adress{Street = “StackOverflow”, App = 3}
p1.Age = 20;

In the database I have created the user defined types, and the stored procedure that takes as parameter a person_type that contains an address_type.

create or replace 
TYPE Person_TYPE AS OBJECT (
    Name     VARCHAR2(100),
    Age      NUMBER,
    OBJ_Address  Adress_TYPE
);

In the SP I have:

function  FC_Insert_Person  ( DATAINS  Person_TYPE ) 
    BEGIN

       INSERT INTO Person ( Name, Age ) VALUES (DATAINS.Name, DATAINS.Age )

     --insert into the nested object
     FC_INSERT_Addrres (DATAINS.OBJ_Address);
     /* in the adress sp I get the id of the person using an SEQ_PERSON_ID.CURRVAL; */

return ...
end FC_Insert_Person;

All the code examples that I find are only for simple objects, or a collection of tables so this makes me ask myself if this will be possible.

OracleParameter[] param = new OracleParameter[1];
                param[0] = new OracleParameter(my_type, OracleDbType.Object, ParameterDirection.Input);
                param[0].UdtTypeName = "Person_TYPE";
                param[0].Value = p1;
                dObj.ExecuteCommand("FC_Insert_Person", param);

Upvotes: 0

Views: 1457

Answers (1)

Christian Shay
Christian Shay

Reputation: 2635

Yes. Use the custom class wizard which is part of Oracle Developer Tools for Visual Studio. A walkthough example is here:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/appdev/dotnet/userdefinedtypes/index.html

Note that it is not advisable to rely on UDTs if performance is critical (eg lots of data is being passed). In that case you should flatten the objects and use associative arrays...or use temporary tables.

Upvotes: 2

Related Questions