I have a SP where I am inserting or uploading some records. So while Inserting if same record is inserted second time, the earlier records gets deleted with other records. I want to keep those records while inserting the new one. Below is the query.
IF TBL_INSERT > 0 THEN
BEGIN
SELECT COUNT(*) INTO V_PANAROMICIMG_COUNT FROM TBL_VSAT_IMAGE_DETAIL WHERE IMG_TYPE = 'Panaromic' AND IMG_ID = TBL_INSERT;
IF V_PANAROMICIMG_COUNT > 0 THEN
BEGIN
DELETE FROM TBL_VSAT_IMAGE_DETAIL WHERE IMG_TYPE = 'Panaromic' AND IMG_ID = TBL_INSERT;
END;
END IF;
IF P_PANORAMIC_IMAGES IS NOT NULL THEN
BEGIN
FOR PMULTIFIELDS IN (SELECT REGEXP_SUBSTR(P_PANORAMIC_IMAGES,'[^,]+', 1, LEVEL) AS IMAGES FROM DUAL
CONNECT BY REGEXP_SUBSTR(P_PANORAMIC_IMAGES, '[^,]+', 1, LEVEL) IS NOT NULL
)
LOOP
INSERT INTO TBL_VSAT_IMAGE_DETAIL
(
IMG_ID,
IMG_NAME,
IMG_TYPE,
IMG_UPLOADED_DATE,
UPLOADED_BY
)
VALUES
(
TBL_INSERT,
PMULTIFIELDS.IMAGES,
'Panaromic',
SYSDATE,
P_CREATED_BY
);
END LOOP;
END;
END IF;