Oracle UDT

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);

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.

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.

We need to define the UDT type too:

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.