Thursday, May 08, 2014

Odp dot Net - Usage and Samples - Part I

I've been working in Oracle - .Net set up for a while and the development experience is totally different to a SQL Server - .Net integration. The learning curve is high, tool set is not good and there are lot of caveats. Hell, the installation of the driver it self can be a nightmare. We have started with Microsoft oracle driver that is obsolete now, then moved to Odp.net (Odp.Net is an implementation of ADO.Net data provider for the Oracle Database by Oracle) which is the focus of this blog post. In our application landscape, Oracle constructs are primarily PL/SQL apis (stored procs).

One thing that has improved over time is the driver, i.e Odp.net. For one, there's some documentation. But looking back one thing that was missing was a set of code samples backed up with explanations to get you started. The official documentation is typical Oracle documentation (i.e lots of content but either they are outdated or hard to understand for a novice, mostly because they use quite out of date .Net idioms), however there's a sample project that's not too bad.

Hopefully this blog will cover some of the most common usage patterns of Odp.Net and get some one rolling faster. (I'm not covering the absolute start of working with scalar/primitive types as it can be found quite easily via Google/SO)

Part 1 - Working with lists (Arrays & User Defined Objects)
Part 2 - Cursors (Both strongly typed & loosely typed)
Part 3 - Xml 

Each data type will be discussed in the form of an Oracle Parameter. This is because, essentially how you create and pass the parameter to Oracle dictates whether you get the integration working or not. It's a lean way to explain the crux of the problem. In general whether the parameter is input or output doesn't matter unless it's explicitly mentioned or is plain obvious (As in the case of setting the value only in input parameters but not in output)

  • Scalar Arrays (Numbers and Varchars) 


At the Oracle End the parameter has to be declared as an Associative Array of Number and Varchar respectively as follows;

TYPE t_id_tab IS TABLE OF NUMBER(20) INDEX BY PLS_INTEGER;
 t_id_tab should be the type of the parameter

TYPE t_string_tab IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;
 t_string_tab should be the type of the parameter


  • Array of complex type / Composite Arrays

This is not possible to do using above techniques as of now. 
For an example, a list of PL/SQL type is not consumable by a .Net application using ODP.Net.  The solution is to use User Defined Objects or UDTs.  UDTs are basically objects created with a global scope in Oracle instance. The PL/SQLs developers were not particularly fans of this approach. I will discuss this approach in detail in the next blog.
Post a Comment