Advanced Oracle UDT

In the last post we saw how to call procedures with simple UDT types (with one field of basic oracle types), but, what happen with complex types defined in oracle? We can use them to pass or return data of this type to Oracle or .net? Obviously the answer is yes. In this post we are going to see how to do it.

The first step is create our “row”. To do it, we need to create an object with the definition of the “columns”. After that we need to create a table of this new object.

As parameter of the procedure, we need to declare the table. As we use the table as parameter we can access directly instead to fill it, etc.

Ok, now the database side is done. Now we need to program the .net part. The first step is do the mapping between our .net “row” and oracle “row”. We need to inherit from TypeTemplate and decore our properties with the name of the property in the oracle “row field”.

At this point, we have our equivalence between oracle “row” and .net “row”. So now we need the equivalence of the “table”. Firstly we need to declare the “row”, because we only have defined the “content” of the row. To declare the row we need to inherit from TypeFactoryTemplate of our row content, and for the table inherits from TableFactoryTemplate of UDT_Table of this new row. That’s can sound difficult but is very easy as you can see in the image.

Now we have all the mapping so it’s time to call the database. As you can until now is easy to implement due to the implemented code in the classes that we use to inherit. We have developed this classes to facilitate the work. Like in the previous mail, you can download the libraries and use it in your project.

Returning to the .net part, we need to create our table: UDT_Table. In the property Value of the table we need to fill it with an array of OurRowClass with the data that we want to send to database. That’s all. We call the procedure and in our OracleParameter we need to specify the UdtTypeName (our table type in oracle) and the table as parameter.

You can download the code here.

Note: We have modified the library to allow complex types and we have reorganized the classes in different namespaces. Now we have OracleParameterBinding.Simple (for simple parameters. The commented in the previous post) and OracleParameterBinding.Complex (complex types), so you can migrate from one version to this new version without problem.