Compare and Merge both table and collection

Viewed 31

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;
0 Answers
Related