Oracle Merge statement error in procedure package body

Question

I'm struggling trying to make this procedure to work, I have the following code inside my package body:

PACKAGE BODY PKG_DM_TRANS_DIMENSIONES AS

          PROCEDURE SP_DM_TRANS_DIM_CUENTA AS

        vNumRegistrosDimCuentas NUMBER; 

          BEGIN
            SELECT COUNT(*) INTO vNumRegistrosDimCuentas
            FROM DIM_CUENTAS;

            IF (vNumRegistrosDimCuentas <> 0) THEN

              MERGE INTO DIM_CUENTAS DIMC
              USING (
                SELECT * FROM (
                  SELECT 
                    DIM.FNT_CUENTA_ID AS DIM_CUENTA_ID,
                    C.CUE_ID AS FNT_CUENTA_ID,
                    R.REG_REGION AS REGION, 
                    P.PAI_PAIS AS PAIS, 
                    E.EDI_NOMBRE_EDIFICIO AS EDIFICIO,
                    C.CUE_CUENTA, 
                    TIC.TIC_TIPO_CONTACTO,
                    C.CUE_STATUS,
                    CASE 
                      WHEN DIM.FNT_CUENTA_ID IS NULL THEN 1
                      WHEN 
                        R.REG_REGION <> DIM.REGION OR
                        P.PAI_PAIS <> DIM.PAIS OR
                        E.EDI_NOMBRE_EDIFICIO <> DIM.EDIFICIO OR
                        C.CUE_CUENTA <> DIM.CUENTA OR
                        TIC.TIC_TIPO_CONTACTO <> DIM.TIPO_CONTACTO
                      THEN 2
                      ELSE 0
                    END AS TIPO_FILA

                  FROM STA_EDIFICIOS_EXTRACCION E

                    LEFT JOIN 
                    STA_PAISES_EXTRACCION P ON E.EDI_PAI_ID = P.PAI_ID
                    LEFT JOIN
                    STA_REGIONES_EXTRACCION R ON P.PAI_REG_ID = R.REG_ID
                    LEFT JOIN 
                    EUB_EDIFICIO_UBICACION EUB ON EUB.EUB_EDI_ID = E.EDI_ID
                    LEFT JOIN 
                    STA_CUENTAS_EXTRACCION C ON C.CUE_EUB_ID = EUB.EUB_ID
                    LEFT JOIN 
                    STA_TIPOS_CONTACTO_EXTRACCION TIC ON TIC.TIC_ID = C.CUE_TIC_ID
                    LEFT JOIN
                      DIM_CUENTAS DIM ON
                    (C.CUE_ID = DIM.FNT_CUENTA_ID AND DIM.CUENTA_STATUS = 1)
                  )
              ) Q
              ON (DIMC.FNT_CUENTA_ID = Q.TIPO_FILA)
              WHEN MATCHED THEN
                INSERT (DIMC.REGION, DIMC.PAIS, DIMC.EDIFICIO, DIMC.CUENTA, DIMC.TIPO_CONTACTO, DIMC.CUENTA_FECHA_CREACION, DIMC.FNT_CUENTA_ID)
                VALUES (Q.REGION, Q.PAIS, Q.EDIFICIO, Q.CUE_CUENTA, Q.TIC_TIPO_CONTACTO, TO_TIMESTAMP(sysdate, 'MM/DD/YYYY HH24:MI:SS'), Q.FNT_CUENTA_ID)
              WHEN NOT MATCHED THEN

                 UPDATE SET DIMC.CUENTA_STATUS = 0 WHERE DIMC.CUENTA_STATUS = 1 -- <- dummy update stmt 

            ELSE ..... -- else statement code working fine...
        END IF;
      END SP_DM_TRANS_DIM_CUENTA;

END PKG_DM_TRANS_DIMENSIONES;

I'm getting erros at the line

MERGE INTO DIM_CUENTAS DIMC

Saying "Statement ignored"

and then, another error at:

INSERT (DIMC.REGION, DIMC.PAIS, DIMC.EDIFICIO, DIMC.CUENTA, DIMC.TIPO_CONTACTO, DIMC.CUENTA_FECHA_CREACION, DIMC.FNT_CUENTA_ID)
        VALUES (Q.REGION, Q.PAIS, Q.EDIFICIO, Q.CUE_CUENTA, Q.TIC_TIPO_CONTACTO, TO_TIMESTAMP(sysdate, 'MM/DD/YYYY HH24:MI:SS'), Q.FNT_CUENTA_ID)

saying "missing keyword". Is it possible to use the merge statement in a SP? I'm new to Oracle so I really don't know if what I'm trying to do is possible or if there's something wrong with my code.

Thanks for any help, I would really appreaciate it.

Answer 1

I think that you swapped commands - after when matched you should put update statement and after not matched - insert. Similar example worked for me, but after swapping statements I got ORA-00905 missing keyword. So correct version is:

merge into t1 
using (select * from t2) t2 on (t1.id = t2.id)
when matched then update set t1.name = t2.name
when not matched then insert (id, name) values (t2.id, t2.name)