This code snippet explains how to apply the merge utility
available in Oracle. Merge proves to be a suitable option where both insert and
update are needed simultaneously. It can insert records into a table while
simultaneously updating the previous records. But the normal operation allows
the record to be peeked up either from table, view or sub query.
But there exist situations where we may require to manipulate
the records before merging into destination table peeked up from the data
sources (table/view/sub_query). The obvious solution seems to be the use of
local variables where picked records can be stored for required manipulation.
Then the merge can be slightly modified to select the manipulated records through
the local variables from dual instead of original table/view/sub_query.
But the major problem arises here, which makes the following
discussion vital.