Oracle Expert

I have worked on a Finance application, where we were actually building a General Ledger application from scratch. Eeeek, you might say, but I was grateful for the experience, as it certainly helped boost my understanding within the Finance area.

There are two main tables which are relevant to this note:

• ACCOUNT_POSTING which contains the individual postings to the general ledger
• ACCOUNT_BALANCE which contains the cummulative daily balance, which needs to be updated in near real-time with the overnight and intraday postings

So at beginning of a working day, we copy previous day balance into current day balance.

When we receive new postings for the ledger, we will need to do the following tasks

• insert the new postings to ACCOUNT_POSTING table
• update the balance

Now updating the balance is not as simple as it sounds

• We need to check for totally new entries (perhaps new chart of accounts, eg) and these would simply need to be inserted to the balance table
• Where there are additional new postings to an existing account, we will need to increase/decrease the value with the value of the new postings

 Two long steps, and time consuming.

In this case, the use of MERGE command allows me to simply write a single block of code which satisfies both requirements (UPSERTS).

NB:

On a different note, we wrote this application on Oracle 9.2.0.7. When the database was upgraded to Oracle 10.2.0.1, to my horror, MERGE was returning the wrong result!!! Raised a TAR with Oracle, and received a suggestion to rewrite the application using separate INSERTS and UPDATES…

Luckily…. after some persuasion, we managed to receive a patch to fix this issue…

Please don’t let this discourage you from using MERGE. It ’s definitely an awesome, very useful feature.

The Oracle MERGE command has come in very handy