Master/Detail Updation: Reflecting Identity Value

Master-Detail is quite easy to explain, but its complexity lies when you’re implementing it particularly when the identity columns rely on database given values. Researching on this topic for several months, days and hours, finally things lightened up for me.

Instead having to rely on an auto increment value, I have to rely on a value set provided via trigger. Have to admit myself that the resource below was very helpful indeed to the quest of solving this problem:

http://www.eggheadcafe.com/forumarchives/NETFrameworkADONET/Nov2005/post24232248.asp
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/4d10fe87-2de8-42a8-8ef9-b9d46c0fd28d

The environment I was working with was Windows XP Pro SP+3, Visual Studio 2005, C#.net, Oracle XE DB, Oracle 10g client, System.Data.OracleCleint and TableAdapters;

To illustrate how I went on, I extracted the following tables from my project:

Journal(journalid*, xsactdate, xsactdesc);
JournalAccount(Journalid*, accountid*, xsactvalue);
PKEY(journal_PKEY);

I had the following triggers written for the Journal & JournalAccount table respectively.

Trigger for Journal Table as:

DECLARE
   nJournal_PKey PKey.Journal_PKey%TYPE;
   nChkJournalID Journal.JournalID%TYPE;
BEGIN
   -- this is to prevent posted Journals from being
   -- updated

   IF ( INSERTING ) THEN
      nChkJournalID := :new.JournalID;
   ELSE
      nChkJournalID := :old.JournalID;
   END IF;

   IF ( INSERTING AND ( :new.JournalID IS NULL ) ) THEN

      SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;

      UPDATE PKey SET Journal_PKey = Journal_PKey + 1;

      :new.JournalID   := nJournal_PKey + 1;
   END IF;
END;

Trigger for JournalAccount Table:

DECLARE
   nJournal_PKey PKey.Journal_PKey%TYPE;
   nChkJournalID Journal.JournalID%TYPE;
BEGIN
   -- this is to prevent posted Journals from being
   -- updated

   IF ( INSERTING ) THEN
      nChkJournalID := :new.JournalID;
   ELSE
      nChkJournalID := :old.JournalID;
   END IF;

   IF ( INSERTING AND ( :new.JournalID IS NULL ) ) THEN

      SELECT Journal_PKey INTO nJournal_PKey FROM PKey FOR UPDATE NOWAIT;

      :new.JournalID   := nJournal_PKey;
   END IF;
END;

Having followed what David Sceppa and Jason Kresowaty alias BinaryCoder wrote in their postings, things were going ok for me, except for the fact that Identities aren’t reflected upon saving to the table. Since the TableAdapters created without the InsertCommand, I have to include it by myself.

There were certain things which I need to modify, particularly Insert Query. Look into my Insert Query and you may see the Oracle Keyword “RETURNING”. The keyword is basically enables us to use it as an output parameter. I have inserted the temporary identity and getting back the value assigned via the trigger as an output parameter.

The following describes the “RETURNING” clause of Oracle: http://www.myoracleguide.com/s/Returning.htm

Finally have to amend the

System.Data.DataRowVersion

to

System.Data.DataRowVersion.Proposed

and the identity field (JournalID in my case)

System.Data.ParameterDirection

to

System.Data.ParameterDirection.InputOutput

The following is the InsertCommand in the TableAdapter for the Journal Table:

this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand(); this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO JOURNALACCOUNT(JOURNALID, ACCOUNTID, XSACTVALUE, DISCOUNTEDPAYMENT) VALUES (:JOURNALID, :ACCOUNTID, :XSACTVALUE,'N') RETURNING JOURNALID INTO :JOURNALID";
this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("ACCOUNTID", System.Data.OracleClient.OracleType.Char, 15, System.Data.ParameterDirection.Input, "ACCOUNTID", System.Data.DataRowVersion.Current, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTVALUE", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.Input, "XSACTVALUE", System.Data.DataRowVersion.Current, false, null));

The following is the insertcommand in the tableadapter for the JournalAccount table:

this._adapter.InsertCommand = new System.Data.OracleClient.OracleCommand();
this._adapter.InsertCommand.Connection = this.Connection;
this._adapter.InsertCommand.CommandText = "INSERT INTO JKSBSCHEMA.JOURNAL(JOURNALID, XSACTDATE, XSACTDESC) VALUES (:JOURNALID, :XSACTDATE, :XSACTDESC) RETURNING JOURNALID INTO :JOURNALID";
this._adapter.InsertCommand.CommandType = System.Data.CommandType.Text;
this._adapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.Both;
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("JOURNALID", System.Data.OracleClient.OracleType.Number, 22, System.Data.ParameterDirection.InputOutput, "JOURNALID", System.Data.DataRowVersion.Proposed, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDATE", System.Data.OracleClient.OracleType.DateTime, 7, System.Data.ParameterDirection.Input, "XSACTDATE", System.Data.DataRowVersion.Current, false, null));
this._adapter.InsertCommand.Parameters.Add(new System.Data.OracleClient.OracleParameter("XSACTDESC", System.Data.OracleClient.OracleType.VarChar, 40, System.Data.ParameterDirection.Input, "XSACTDESC", System.Data.DataRowVersion.Current, false, null));

Refer My Post @ MSDN Forums: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/fbfdc09b-41bf-4290-9dea-6a9a85d5becf

The Banker Quote

While browsing the internet, I came up with an interesting quote by Mark Twain which apparently was highlighted under an individuals signature. Check this out:

A banker is a fellow who lends you his umbrella when the sun is shining, but wants it back the minute it begins to rain.
Mark Twain (1835 - 1910)

ORA-06502: PL/SQL: numeric or value error

This error was a real nightmare for me where I'm being in middle of a migration process of our legacy system. Though there was real hits when you google this problem, but nothing was helpfull for me.

The context of the problem occurs when I did run it on a WinXP pro sp3+ workstation which had installed the latest ODP.NET. However its quite extraordinary to see it was not reproduced in a machine installed Oracle Express Edition 11g (without oracle client).

Later, I understood that this problems occurs if you have a VarChar2 parameter(s) when executing a Oracle Stored Procedure. So people advised to have the size and Parameter direction in the parameter. I incresed the size everytime of the parameter and had the parameter direction as "input", yet this error kept bugging me. (My stored procedures did not have any return/output parameters, so I'm not in a possition to comment its behavior).

Finally after googling for days and finding one post in the Oracle forum from one guy who said to use the direction as "InputOutput" did the work for me. Furthermore to the parameter direction, I also gave all of the VarChar2 parameter size as 2000.

Produced Environment: .NET framework 2.0, Win XP Pro sp3 and the latest ODP.NET