Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MERGE with PL/SQL Record As Source

413063Jun 13 2006 — edited Jun 14 2006
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 12 2006
Added on Jun 13 2006
5 comments
822 views