ora-30926 unable to get a stable set of rows in the source tables
vissuOct 27 2010 — edited Oct 27 2010Hi all,
I am stucking with an issue 'ora-30926 unable to get a stable set of rows in the source tables' with one of the MERGE Statement in the procedure.
MERGE INTO CMS_EQUIPMENT EQ
USING
(SELECT DISTINCT VPF.VPF_COMPANY_NR,trim(STG.EQUIPMENT_ID) as EQUIPMENT_ID,
STG.EQUIPMENT_TYPE,NVL(STG.INSTALLATION_DATE,STG.EFFECTIVE_DATE) AS INSTALLATION_DATE,
STG.PROJECTOR_MODEL,STG.TI_SOFTWARE,STG.PROJECTOR_SERIAL,
STG.SERVER_SOFTWARE_VERSION,STG.CERTIFICATE_NUMBER,
STG.MB_FIRMWARE_VERSION,STG.MB_LINK_ENCRYPTION,
STG.WATERMARKING,STG.SYSTEM3D,STG.SERVER_MODEL,STG.SERVER_SERIAL
FROM CMS_EQUIP_STAGING STG,
CMS_VPF_COMPANY VPF
WHERE UPPER(TRIM(STG.VPF_COMPANY_NAME))=UPPER(TRIM(VPF.VPF_COMPANY_NAME ))
AND UPPER(TRIM(STG.DATA_LOAD_STATUS))='NEW'
) ESTG
ON
(upper(trim(ESTG.EQUIPMENT_ID))=upper(trim(EQ.EQUIPMENT_ID))
AND EQ.VPF_COMPANY_NR=ESTG.VPF_COMPANY_NR)
WHEN MATCHED THEN
UPDATE SET
EQ.VPF_COMPANY_EQUIPMENT_ID=ESTG.EQUIPMENT_ID,
EQ.VPF_COMPANY_EQUIP_TYPE=ESTG.EQUIPMENT_TYPE,
EQ.EQUIP_EFFECTIVE_DATE=ESTG.INSTALLATION_DATE,
EQ.PROJECTOR_MODEL=ESTG.PROJECTOR_MODEL,
EQ.TI_SOFTWARE=ESTG.TI_SOFTWARE,
EQ.PROJECTOR_SERIAL=ESTG.PROJECTOR_SERIAL,
EQ.SERVER_MODEL=ESTG.SERVER_MODEL,
EQ.SERVER_SERIAL=ESTG.SERVER_SERIAL,
EQ.SERVER_SOFTWARE_VERSION=ESTG.SERVER_SOFTWARE_VERSION,
EQ.CERTIFICATE_NUMBER=ESTG.CERTIFICATE_NUMBER,
EQ.MB_FIRMWARE_VERSION=ESTG.MB_FIRMWARE_VERSION,
EQ.MB_LINK_ENCRYPTION=ESTG.MB_LINK_ENCRYPTION,
EQ.WATERMARKING=ESTG.WATERMARKING,
EQ.SYSTEM3D=ESTG.SYSTEM3D,
EQ.VERSION_ID= CASE WHEN EQ.VPF_COMPANY_EQUIPMENT_ID <> ESTG.EQUIPMENT_ID OR
EQ.VPF_COMPANY_EQUIP_TYPE <> ESTG.EQUIPMENT_TYPE OR
EQ.EQUIP_EFFECTIVE_DATE <> ESTG.INSTALLATION_DATE OR
EQ.PROJECTOR_MODEL <> ESTG.PROJECTOR_MODEL OR
EQ.TI_SOFTWARE <> ESTG.TI_SOFTWARE OR
EQ.PROJECTOR_SERIAL <> ESTG.PROJECTOR_SERIAL OR
EQ.SERVER_MODEL <> ESTG.SERVER_MODEL OR
EQ.SERVER_SERIAL <> ESTG.SERVER_SERIAL OR
EQ.SERVER_SOFTWARE_VERSION <> ESTG.SERVER_SOFTWARE_VERSION OR
EQ.CERTIFICATE_NUMBER <> ESTG.CERTIFICATE_NUMBER OR
EQ.MB_FIRMWARE_VERSION <> ESTG.MB_FIRMWARE_VERSION OR
EQ.MB_LINK_ENCRYPTION <> ESTG.MB_LINK_ENCRYPTION OR
EQ.WATERMARKING <> ESTG.WATERMARKING OR
EQ.SYSTEM3D <> ESTG.SYSTEM3D
THEN EQ.VERSION_ID+1 ELSE EQ.VERSION_ID END,
EQ.UPDATED_DATE= CASE WHEN EQ.VPF_COMPANY_EQUIPMENT_ID <> ESTG.EQUIPMENT_ID OR
EQ.VPF_COMPANY_EQUIP_TYPE <> ESTG.EQUIPMENT_TYPE OR
EQ.EQUIP_EFFECTIVE_DATE <> ESTG.INSTALLATION_DATE OR
EQ.PROJECTOR_MODEL <> ESTG.PROJECTOR_MODEL OR
EQ.TI_SOFTWARE <> ESTG.TI_SOFTWARE OR
EQ.PROJECTOR_SERIAL <> ESTG.PROJECTOR_SERIAL OR
EQ.SERVER_MODEL <> ESTG.SERVER_MODEL OR
EQ.SERVER_SERIAL <> ESTG.SERVER_SERIAL OR
EQ.SERVER_SOFTWARE_VERSION <> ESTG.SERVER_SOFTWARE_VERSION OR
EQ.CERTIFICATE_NUMBER <> ESTG.CERTIFICATE_NUMBER OR
EQ.MB_FIRMWARE_VERSION <> ESTG.MB_FIRMWARE_VERSION OR
EQ.MB_LINK_ENCRYPTION <> ESTG.MB_LINK_ENCRYPTION OR
EQ.WATERMARKING <> ESTG.WATERMARKING OR
EQ.SYSTEM3D <> ESTG.SYSTEM3D
THEN SYSDATE ELSE EQ.UPDATED_DATE END,
EQ.UPDATED_USER= CASE WHEN EQ.VPF_COMPANY_EQUIPMENT_ID <> ESTG.EQUIPMENT_ID OR
EQ.VPF_COMPANY_EQUIP_TYPE <> ESTG.EQUIPMENT_TYPE OR
EQ.EQUIP_EFFECTIVE_DATE <> ESTG.INSTALLATION_DATE OR
EQ.PROJECTOR_MODEL <> ESTG.PROJECTOR_MODEL OR
EQ.TI_SOFTWARE <> ESTG.TI_SOFTWARE OR
EQ.PROJECTOR_SERIAL <> ESTG.PROJECTOR_SERIAL OR
EQ.SERVER_MODEL <> ESTG.SERVER_MODEL OR
EQ.SERVER_SERIAL <> ESTG.SERVER_SERIAL OR
EQ.SERVER_SOFTWARE_VERSION <> ESTG.SERVER_SOFTWARE_VERSION OR
EQ.CERTIFICATE_NUMBER <> ESTG.CERTIFICATE_NUMBER OR
EQ.MB_FIRMWARE_VERSION <> ESTG.MB_FIRMWARE_VERSION OR
EQ.MB_LINK_ENCRYPTION <> ESTG.MB_LINK_ENCRYPTION OR
EQ.WATERMARKING <> ESTG.WATERMARKING OR
EQ.SYSTEM3D <> ESTG.SYSTEM3D
THEN G_JOB_NAME ELSE EQ.UPDATED_USER END
WHEN NOT MATCHED THEN
INSERT
(EQ.EQUIPMENT_NR,
EQ.EQUIPMENT_ID,
EQ.VPF_COMPANY_NR,
EQ.VPF_COMPANY_EQUIPMENT_ID,
EQ.VPF_COMPANY_EQUIP_TYPE,
EQ.EQUIP_EFFECTIVE_DATE,
EQ.PROJECTOR_MODEL,
EQ.TI_SOFTWARE,
EQ.PROJECTOR_SERIAL,
EQ.SERVER_MODEL,
EQ.SERVER_SERIAL,
EQ.SERVER_SOFTWARE_VERSION,
EQ.CERTIFICATE_NUMBER,
EQ.MB_FIRMWARE_VERSION,
EQ.MB_LINK_ENCRYPTION,
EQ.WATERMARKING,
EQ.SYSTEM3D,
EQ.VERSION_ID,
EQ.CREATED_DATE,
EQ.CREATED_USER,
EQ.UPDATED_DATE,
EQ.UPDATED_USER
)
VALUES
(
CMS_EQUIPMENT_NR_SEQ.NEXTVAL,
ESTG.EQUIPMENT_ID,
ESTG.VPF_COMPANY_NR,
ESTG.EQUIPMENT_ID,
ESTG.EQUIPMENT_TYPE,
ESTG.INSTALLATION_DATE,
ESTG.PROJECTOR_MODEL,
ESTG.TI_SOFTWARE,
ESTG.PROJECTOR_SERIAL,
ESTG.SERVER_MODEL,
ESTG.SERVER_SERIAL,
ESTG.SERVER_SOFTWARE_VERSION,
ESTG.CERTIFICATE_NUMBER,
ESTG.MB_FIRMWARE_VERSION,
ESTG.MB_LINK_ENCRYPTION,
ESTG.WATERMARKING,
ESTG.SYSTEM3D,
1,
SYSDATE,
G_JOB_NAME,
SYSDATE,
G_JOB_NAME
);
Please help me how to fix out it. Would be appreciated for your instant help!!
Oracle version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
Regards,
Vissu....