Category: Utils

Advanced Oracle UDT

31 Jul 2014 Miguel Ángel Utiel Peñaranda Leave a comment

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.

1

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.

1.1

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”.

2

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.

3

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.

4

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.

Oracle UDT

4 Jul 2014 Miguel Ángel Utiel Peñaranda Leave a comment

Have you ever been passed a list of parameters to Oracle Procedure? Some people use arrays to pass list of parameters to the procedures but Oracle has a more powerful option to do this, the UDT types (User Define Types). In this post we are to see a basic example to do this.

Currently, if you want to do this, the first step is to create an array as IN parameters, create a table with the same type that the elements into the array, fill this table and use it in the query. In .net you need to pass the array as value of the OracleParameter:

create or replace TYPE LIST_ITEM_STRING IS TABLE OF VARCHAR2(20);

1

2

There are another way to do this more directly. We will use the UDT types. In this case we can use the table directly instead use an array and fill the table in the procedure.

3

In .net part we need to assign the type used for the table, in our case the type defined above (HR.LIST_ITEM_STRING). Here the type would be Object and the value a “TableTemplate” of string.

4

We need to define the UDT type too:

5

OracleParameterBinding: We need to do the mapping between Oracle and .Net (read and write). This can be a little dificult to implement, in fact, a lot of documentation that i have read about that is a hell. We have created this library to do it more easy. As you can see your value only need to be an array of TableTemplate and that’s it. Easy, right? Feel free to use the library in your projects if you want, the library is included in the source code bellow. In addition, you can contribute with our paypal account ;).

You can download the code here.