I want to compare data from table and collection both. I am using merge to compare the data from the collection to the stored table, but it is giving error of missing expression and I have to update data only when it is not matched. What is the solution for this. As data copied from one collection to another collection in the below code and then comparing from the table. Please find the below code.
CREATE OR REPLACE PACKAGE BODY PKG_USERRECORD IS
CONST_SUCCESS CONSTANT CHAR(1) := 'S';
CONST_FAILURE CONSTANT CHAR(1) := 'F';
PROCEDURE PR_CREATE_USERRECORD_STRING(PC_USER_INPUT IN VARCHAR2,
PC_STATUS OUT VARCHAR2) IS
CURSOR CUR_USERSTRING IS
SELECT REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 1, NULL, 1) USERID,
REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 2, NULL, 1) USERNAME,
REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 3, NULL, 1) DATE_OF_JOINING,
REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 4, NULL, 1) CREATED_BY,
REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 5, NULL, 1) CREATION_DATE,
REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 6, NULL, 1) MODIFIED_BY,
REGEXP_SUBSTR(USER_INPUT, '([^~]*)(~|$)', 1, 7, NULL, 1) MODIFIED_DATE
FROM (
SELECT REGEXP_SUBSTR(PC_USER_INPUT, '[^^]+', 1, LEVEL) USER_INPUT
FROM DUAL
CONNECT BY LEVEL <= LENGTH(PC_USER_INPUT) -
LENGTH(REPLACE(PC_USER_INPUT, '^')) + 1
);
TYPE T_CUR_USERSTRING IS TABLE OF CUR_USERSTRING%ROWTYPE;
T_USERRECORD T_CUR_USERSTRING;
T_USERRECORD_INSERT T_CUR_USERSTRING := T_CUR_USERSTRING();
T_USERRECORD_UPDATE T_CUR_USERSTRING := T_CUR_USERSTRING();
PROCEDURE PR_CLEANUP IS
BEGIN
BEGIN
IF CUR_USERSTRING%ISOPEN THEN
CLOSE CUR_USERSTRING;
END IF;
T_USERRECORD.DELETE;
T_USERRECORD_INSERT.DELETE;
T_USERRECORD_UPDATE.DELETE;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
BEGIN
OPEN CUR_USERSTRING;
LOOP
--T_USERRECORD.DELETE;
FETCH CUR_USERSTRING BULK COLLECT
INTO T_USERRECORD; --LIMIT 1;
IF T_USERRECORD.COUNT = 0 THEN
EXIT;
END IF;
FOR I IN T_USERRECORD.FIRST .. T_USERRECORD.LAST LOOP
IF T_USERRECORD(I).USERID IS NULL THEN
T_USERRECORD_INSERT.EXTEND;
T_USERRECORD_INSERT(T_USERRECORD_INSERT.COUNT) := T_USERRECORD(I);
ELSE
T_USERRECORD_UPDATE.EXTEND;
T_USERRECORD_UPDATE(T_USERRECORD_UPDATE.COUNT) := T_USERRECORD(I);
END IF;
END LOOP;
FORALL N IN T_USERRECORD_INSERT.FIRST .. T_USERRECORD_INSERT.LAST
-- INSERT INTO QM_USERDATA VALUES T_USERRECORD_INSERT (N);
INSERT INTO QM_USERDATA
(USERID,
USERNAME,
DATE_OF_JOINING,
CREATED_BY,
CREATION_DATE,
MODIFIED_BY,
MODIFIED_DATE)
VALUES
(SEQ_QM_USERDATA.NEXTVAL,
T_USERRECORD_INSERT(N).USERNAME,
T_USERRECORD_INSERT(N).DATE_OF_JOINING,
T_USERRECORD_INSERT(N).CREATED_BY,
T_USERRECORD_INSERT(N).CREATION_DATE,
T_USERRECORD_INSERT(N).MODIFIED_BY,
T_USERRECORD_INSERT(N).MODIFIED_DATE);
FORALL M IN T_USERRECORD_UPDATE.FIRST .. T_USERRECORD_UPDATE.LAST
MERGE INTO QM_USERDATA U
USING (select T_USERRECORD_UPDATE(M).USERID AS USERID,
T_USERRECORD_UPDATE(M).USERNAME AS USERNAME,
T_USERRECORD_UPDATE(M).DATE_OF_JOINING AS DATE_OF_JOINING,
T_USERRECORD_UPDATE(M).CREATED_BY AS CREATED_BY,
T_USERRECORD_UPDATE(M).CREATION_DATE AS CREATION_DATE,
T_USERRECORD_UPDATE(M).MODIFIED_BY AS MODIFIED_BY,
T_USERRECORD_UPDATE(M).MODIFIED_DATE AS MODIFIED_DATE
FROM DUAL) UPDATE_MERGE
ON (U.USERID = UPDATE_MERGE.USERID)
WHEN MATCHED THEN
UPDATE
SET U.USERNAME = UPDATE_MERGE.USERNAME,
U.DATE_OF_JOINING = UPDATE_MERGE.DATE_OF_JOINING,
U.CREATED_BY = UPDATE_MERGE.CREATED_BY,
U.CREATION_DATE = UPDATE_MERGE.CREATION_DATE,
U.MODIFIED_BY = UPDATE_MERGE.MODIFIED_BY,
U.MODIFIED_DATE = UPDATE_MERGE.MODIFIED_DATE;
END LOOP;
CLOSE CUR_USERSTRING;
-- COMMIT;
PR_CLEANUP;
PC_STATUS := CONST_SUCCESS;
EXCEPTION
WHEN OTHERS THEN
PR_CLEANUP;
PC_STATUS := CONST_FAILURE;
PR_ERROR_LOGGING(SQLCODE || ' ' || SUBSTR(SQLERRM, 0, 3000),
'PR_CREATE_USERRECORD_STRING');
END PR_CREATE_USERRECORD_STRING;
END PKG_USERRECORD;