Code Example: MERGE is same as INSERT with UPDATE

======================
General
======================

There are Errors during MERGE statement:
ORA-00600: internal error code, arguments: [13013], [5001], [32001], [37749294], [16], [37749294], [17], [], [], [], [], []

These errors are related to :
Ora 600 [13013] DURING MERGE (Doc ID 1331453.1) Due to Oracle Bug #12345717 

======================
Solution
======================

Option A.

Apply patch  p12345717

DATABASE PATCH SET UPDATE 11.1.0.7.24 (INCLUDES CPUJUL2015) (Patch)

p12345717_1110715_Linux-x86-64.zip - MERGE FAILS WITH ORA-600 [13013] (Patch)

PSU 11.1.0.7.24 is pre-requisit for p12345717

Option B
DROP and CREATE the target table.

Option C.
If applying patch is not possible, replace MERGE with UPDATE and INSERT.

======================
Code Example
======================

PROCEDURE Merge_Campaigns(p_Day_To_Process IN VARCHAR2) IS
    -- This function must run within a separate transaction to enable trigger usage.
    PRAGMA AUTONOMOUS_TRANSACTION;
    
    CURSOR get_upd_campaigns_cur IS
     SELECT campaign_id, category_id, campaign_name, status,
            subscriber_req_flag, welcome_back_flag,
            default_campaign_flag, item_id
       FROM TEMP_PSMS_CAMPAIGNS
      WHERE TEMP_PSMS_CAMPAIGNS.campaign_id IN (SELECT DIM_CAMPAIGNS.campaign_id FROM DIM_CAMPAIGNS);

    v_counter        NUMBER;

BEGIN
     --Write StartTime of procedure
    GENERAL_REPORTS_PKG.write_log
    ('Reports_Provisioning_Pkg.Nerge_Campaigns',
     'Started: ' ||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS'));
                                         
    GENERAL_REPORTS_PKG.write_log('Reports_Provisioning_Pkg.Merge_Campaigns', 
                                  'Before MERGE INTO dim_campaigns');
   -- for now fetch only outbound campaign - as we don't have DWH for inbound
      v_counter := 0;
      FOR get_upd_campaigns_rec IN get_upd_campaigns_cur LOOP
        UPDATE DIM_CAMPAIGNS tgt
           SET tgt.category_id           = get_upd_campaigns_rec.category_id,
               tgt.campaign_name         = get_upd_campaigns_rec.campaign_name,
               tgt.activation_flag       = get_upd_campaigns_rec.status,
               tgt.subscriber_req_flag   = get_upd_campaigns_rec.subscriber_req_flag,
               tgt.welcome_back_flag     = get_upd_campaigns_rec.welcome_back_flag,
               tgt.default_campaign_flag = get_upd_campaigns_rec.default_campaign_flag,
               tgt.item_id               = get_upd_campaigns_rec.item_id
         WHERE tgt.campaign_id           = get_upd_campaigns_rec.campaign_id;
         v_counter := v_counter + 1;
      END LOOP;  
      COMMIT;
      GENERAL_REPORTS_PKG.write_log('Reports_Provisioning_Pkg.Merge_Campaigns', 
                                    'Number of UPDATED Records: '||v_counter);
      
      INSERT INTO DIM_CAMPAIGNS tgt 
                  (campaign_id, campaign_name,  category_id,  
                   activation_flag,  criteria_sql,  
                   roamer_criteria_sql,  is_deleted,  deletion_date,
                   subscriber_req_flag,  welcome_back_flag,  
                   default_campaign_flag,  item_id)   
           SELECT campaign_id, campaign_name, category_id, status, NULL,
                  NULL, NULL, NULL, subscriber_req_flag, welcome_back_flag,
                  default_campaign_flag, item_id
             FROM temp_psms_campaigns src
           WHERE  src.campaign_id NOT IN (SELECT campaign_id FROM dim_campaigns);
      COMMIT;
      
      GENERAL_REPORTS_PKG.write_log('Reports_Provisioning_Pkg.Merge_Campaigns', 
                                    'Number of INSERTED Records: '||SQL%ROWCOUNT);

      /*
      MERGE INTO DIM_CAMPAIGNS tgt
      USING (SELECT campaign_id, category_id, campaign_name, status,
                    subscriber_req_flag, welcome_back_flag,
                    DEFAULT_CAMPAIGN_FLAG, item_id
               FROM temp_psms_campaigns) src
      ON (src.campaign_id = tgt.campaign_id)
      WHEN MATCHED THEN
        UPDATE
           SET tgt.category_id = src.category_id,
               tgt.campaign_name = src.campaign_name,
               tgt.activation_flag = src.status,
               tgt.subscriber_req_flag = src.subscriber_req_flag,
               tgt.welcome_back_flag = src.welcome_back_flag,
               tgt.default_campaign_flag = src.default_campaign_flag,
               tgt.item_id = src.item_id
      WHEN NOT MATCHED THEN
        INSERT
          (tgt.campaign_id, tgt.category_id, tgt.campaign_name,
           tgt.activation_flag, tgt.subscriber_req_flag,
           tgt.welcome_back_flag, tgt.default_campaign_flag, tgt.item_id)
        VALUES
          (src.campaign_id, src.category_id, src.campaign_name, src.status,
           src.subscriber_req_flag, src.welcome_back_flag,
           src.default_campaign_flag, src.item_id);
      Commit;
      */
      
      GENERAL_REPORTS_PKG.write_log('Reports_Provisioning_Pkg.Merge_Campaigns', 
                                    'After MERGE INTO dim_campaigns');
=========================================================================================