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.

Oracle 9i or above versions

Let us examine the concept with the following listing.

Listing 1

CREATE OR REPLACE
 PROCEDURE merge_employee_p
  as
    Employee_no     emp.EMPLOYEE_ID%type := 0;
    Employee_name   emp.FIRST_NAME%type := '';
    Employee_job    emp.JOB_ID%type := '';
    Employee_dept   emp.DEPARTMENT_ID%type := 0;
    Error_code      varchar2(400);
    Error_message   varchar2(400);
    counter         number :=0;
 begin
       dbms_output.put_line('selection of records for processing started');
       
       For process_record in 
           (
             select EMPLOYEE_ID,FIRST_NAME,JOB_ID,DEPARTMENT_ID
             from emp
           )
       Loop
                 Employee_name := 'MR.'||process_record.FIRST_NAME;
            Employee_no   := process_record.EMPLOYEE_ID;
            Employee_job  := process_record.JOB_ID;
            Employee_dept := process_record.DEPARTMENT_ID;
            dbms_output.put_line('loop'||counter);
       MERGE INTO merge_employee a
       USING (                      
       SELECT
                        Employee_name Employee_name,/*Employee_name is the alias*/
                        Employee_no Employee_no,
                        Employee_job Employee_job,
                        Employee_dept Employee_dept
                        FROM dual
             ) p
        ON (a.EMPLOYEE_ID = p.Employee_no)
        WHEN MATCHED THEN UPDATE SET
                        a.FIRST_NAME    = p.Employee_name,
                        a.JOB_ID        = p.Employee_job
                        a.DEPARTMENT_ID = p.Employee_dept
        WHEN NOT MATCHED THEN INSERT
        (
                        a.EMPLOYEE_ID,
                        a.FIRST_NAME,
                        a.JOB_ID,
                        a.DEPARTMENT_ID
        )
        VALUES
        (
                        p.Employee_no,
                        p.Employee_name,
                        p.Employee_job,
                        p.Employee_dept
        );
 
        counter:=counter+1;
        dbms_output.put_line('successfully insert completed for record no :'||counter);
        end loop;
 
        EXCEPTION
        WHEN OTHERS THEN
        error_code    := SQLCODE;
        error_message := SQLERRM;
        dbms_output.put_line('failed due to '|| SQLERRM||'        '||SQLCODE);
  end  merge_employee_p;

Explanation

Here the records are being picked from emp table. To make the example easy, only four fields (EMPLOYEE_ID, FIRST_NAME, JOB_ID, DEPARTMENT_ID) from emp table have been used as source data. Further, to make the scenario lively, the "FIRST_NAME" field has been manipulated before being merged into a test table "merge_employee."

This requires the data from the source to be picked record wise one after the next. As the records are pulled into the local variables one at a time, they are manipulated and merged into the destination table through a loop, one insertation or updation per iteration.

But the crucial point here is that we are not peeking up records from any table/view/sub_query, instead they are from the dual table which picks the local variables found within the scope of current iteration. The use of column alias in selecting the local variables from dual is the solution to the problem raised in the introduction part.

The interesting thing is that any fail to declare the alias in the select clause will not face any syntactical error as expected. But there seems to be an inherent problem with the merge without alias. It throws a runtime error as mentioned below.

Figure 1

Indicating that the scope of the variables is not powerful enough demands the use of aliasing. The user can run the same piece of code without the alias in order to find the difference.    

Structures of source table

Figure 2

Execution of the procedure

Figure 3

.

.

Structures of destination table after merging is done (The first names have been appended)

Figure 4

The alternate solution to the use of merge may be to declare any column in the destination table as the primary key. Then start inserting the record into the table for fresh ones and use the "dup val on index" error returned by Oracle to update the previously present records. But in case we cannot declare any the primary key for business point of view, the idea will not work. Therefore, the merge method looks obviously good and efficient in all circumstances.