0

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);
GeorgesC
  • 145
  • 4
  • 15
  • 1
    Yes. Use the custom class wizard which is part of Oracle Developer Tools for Visual Studio. Walkthough example is here: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/appdev/dotnet/userdefinedtypes/index.html – Christian Shay Jan 17 '17 at 20:47
  • 1
    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. – Christian Shay Jan 17 '17 at 20:49
  • I agree with you, after some days of search I found out the oracle's page using VS2013 that automatically generates the UDT classes but as I use VS2012 :( I've given up. But found out as you mentioned that this will not be the most efficient method to do inserts for large objects. So I’ll tick to the basics: One type one insert procedure. – GeorgesC Jan 23 '17 at 11:42

1 Answers1

2

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.

Christian Shay
  • 2,570
  • 14
  • 24