Oracle ODP.NET

I'm working now on a new project that in internals use ODP.NET - ORACLE Data Provider for .NET and latest Oracle 11g.

First of all I want to mention that ODP.NET is a great disappointment for me. In writing of very simple a stupid code ODP give me so many issues that I start to think that ORACLE completely loose own mind and release very unstable library for developers. I loose 2 days for fighting with found issues and I hope my article will help others a lot.

Simple Scenario that does not work in ODP.NET

Let's start from the simple scenario: Create typed DataSet with several tables, infill dataset by the data and update database by that data. Primary keys of the rows must be updated by Adapter automatically on Update method call.

---

Step #1:

I start implementation from defining tables on ORACLE side by SQLDeveloper tool. That part was easy enough with exception of auto incremented primary keys implementation. But here me helps a lot a great article: http://jen.fluxcapacitor.net/geek/autoincr.html
Tips: SQL Developer already has a defined template for that operation, but I found that at the design of the last table. Bad for me.

Step #2:

Define Typed Dataset. That was really easy. I configure Oracle Connection in visual studio and just drag&drop tables into dataset. Then I decide to check and that was i really good move. I found that Dataset well catch primary keys and relations, but auto increment fields not catched well.
So I make small modification to all primary keys columns. I set properties:
AutoIncrement - "True"
AutoIncrementSeed - "-1"
AutoIncrementStep - "-1"

Step #3:

Define INSERT commands that returns us Primary key value from DB side. It was not easy task, but another article helps me a lot: http://foxsys.blogspot.com/2007/06/oracle-newly-inserted-row-using.html

After reading that article INSERT creation become very easy.

But at that point I found several interesting issues:

issue #1:

on INSERT command execute oracle throw me exception ORA-01722.

4 hours of fiting with that issue give me a solution: http://tgaw.wordpress.com/2006/03/03/ora-01722-with-odp-and-command-parameters/
Property of the OracleCommand.BindByName must be set to "True" value, otherwise oracle implementation uses internal indexes for sending/getting parameters in order that it think is right.

issue #2:

By default OracleCommandBuilder that creates Commands set property UpdateRowSource to "None" value. Such behavior force me to spent 4 hours for finiding that solution.

My question: what for Microsoft design concept of parametrized queries if it not used by others?! Why default parameters of the classes in ORACLE implementation are so different in compare to Microsoft implementation?

Real shame to the ORACLE developers that don't know how to set correctly default parameters.

Step #4:

Create DataAdapter and update DB by infilled data. What can be easier you ask? This is the point where you are wrong.

issue #3

First run of the code and you got nice exception:

System.ArgumentException occurred
  Message="Unable to cast object of type 'Oracle.DataAccess.Types.OracleDecimal' to type 'System.IConvertible'.Couldn't store <28> in ID Column.  Expected type is Decimal."

make a little search on Google and you will see on ORACLE forum that this issues not resolved more then 2 years!!! wow!
http://forums.oracle.com/forums/thread.jspa?threadID=613812&tstart=0&messageID=2467419

Are you still searching for solution? Ok, I'll help - I found the solution. Just add handler to the event DataTable.ColumnChanging and in event handler convert Oracle data types to the standard, well convertible types.

VB.NET:
      AddHandler table.ColumnChanging, AddressOf OracleConvert_ColumnChanging

   Private Sub OracleConvert_ColumnChanging(ByVal sender As Object, ByVal e As DataColumnChangeEventArgs)
      If TypeOf e.ProposedValue Is OracleDecimal Then
         e.ProposedValue = CType(e.ProposedValue, OracleDecimal).Value
      End If
   End Sub

After that fix my journey of ODP.NET simple usage scenario is over. or not?

At the end of the day

I hope that simple article helps you a little with ODP.NET and ADO.NET.

I still have one more issue: DataAdapter.Update command does not update relations for other rows. This is a new quest for me...

BTW if you will try to do the same thing by using System.Data.Oracle provided by Microsoft everything will work fine from the first run.