MERGE with PL/SQL Record As Source
413063Jun 13 2006 — edited Jun 14 2006I have a very complex process that populates a PL/SQL record. This resulting record is identical to a database table and, once built, corresponds directly to 1 row of contents for the database table. The record that is populated is even declared by binding it to the tables ROWTYPE, e.g. lrec_OracleCitation OD_CITATION_STAGING%ROWTYPE.
The record that is built may or may not already be present in the database table. If it is present, I want to UPDATE using the record. If it isn't present, I want to do an INSERT. Sounds like a perfect opportunity for a MERGE statement, right?
So, here's my question, I already have the info I need in the reocrd, can I use the in the MERGE? If so, how do I reference it?
MERGE INTO OD_CITATION_STAGING
USING lrec_OracleCitation
ON ( OD_CITATION_STAGING.CITATION_NO = lrec_OracleCitation.CITATION_NO )
etc.
This obviously doesn't work. Anyway to use a some sort of Collection Pseudo-Function or something to make it appear as a table to the MERGE statement?
-Joe