Monday, May 19, 2014

ODP dot Net - Usage and Samples - Part II

In Part 1 of this blog post series we discussed how we to handle Arrays of simple types with ODP.Net. In this post we''ll discuss how to extend this to complex types by using Oracles' User Defined Types (UDT).

UDTs allows a PL/SQL developer to expose complex db types to outside world above and beyond the PL/SQL layer. The reason for this is because ODP.Net does not support PL/SQL tables as of yet. So to represent a list of a complex object UDTs has to be used. 

There are few additional requirements from the .Net developer to get this working;

  • Mapping classes have to be written for the UDT type and the Array of UDT type
  • Factory classes have to be written (for creation of) for both the above types.
  • The parameter needs to be created with additional property 'UdtTypeName' set. 


The mapping classes and the factory classes used 2 base classes called TypeTemplate, TypeFactoryTemplate and TableTemplate, TableFactoryTemplate classes respectively. These 2 classes abstract away the actual mapping business between the 2 paradigms from the rest of your data access/domain code base. (http://developergeeks.com/article/3/user-defined-type-support-in-oracle-odp-net-11g) 

In addition couple of custom attribute classes are also shown. These can be used to enforce/migrate basic rules like Nullabilitiy of a property to the .Net type. 

These set of classes can be put in a common class library like xxx.Common.Core to be used by multiple projects. 


In the part III of this blog post we will look at a cool new feature of Oracle 11g - Loosely typed cursors and how we can use it with Odp.Net. 
Post a Comment