Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE QM_USERDATA (USERID NUMBER,
  USERNAME VARCHAR2(8), DATE_OF_JOINING DATE,
  CREATED_BY VARCHAR2(8), CREATION_DATE DATE,
  MODIFIED_BY VARCHAR2(8), MODIFIED_DATE DATE)
CREATE SEQUENCE QM_USERDATA_SEQ START WITH 42;
CREATE TRIGGER QM_USERDATA_TRIG BEFORE INSERT ON QM_USERDATA FOR EACH ROW
BEGIN
  :NEW.USERID := QM_USERDATA_SEQ.NEXTVAL;
END;
/
CREATE OR REPLACE PACKAGE PKG_USERRECORD IS
    
--  PROCEDURE PR_CREATE_USERRECORD_STRING(PC_STATUS     OUT VARCHAR2,
--                                        PC_MESSAGE    OUT VARCHAR2);
    
  PROCEDURE PR_CREATE_USERRECORD_STRING(PC_USER_INPUT IN VARCHAR2,
                                        PC_STATUS     OUT VARCHAR2,
                                        PC_MESSAGE OUT VARCHAR2);
END PKG_USERRECORD;
/
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,
                                        PC_MESSAGE 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_USERRECORD_UPDATE T_CUR_USERSTRING;
  
  BEGIN
  
    -- initialiase insert/update collections (could be done in declaration too)
    T_USERRECORD_INSERT := T_CUR_USERSTRING();
    T_USERRECORD_UPDATE := T_CUR_USERSTRING();
    
    OPEN CUR_USERSTRING;
    LOOP
      -- no need to delete here
      -- T_USERRECORD.DELETE;
      FETCH CUR_USERSTRING BULK COLLECT
        INTO T_USERRECORD;
    
      IF T_USERRECORD.COUNT = 0 THEN
        EXIT;
      END IF;
    
      FOR I IN T_USERRECORD.FIRST .. T_USERRECORD.LAST LOOP
      
--A. IF THIS IS A NEW RECORD - POPULATE A SEPARATE COLLECTION FOR INSERTS
--B. IF THIS IS AN EXISTING RECORD - POPULATE A SEPARATE COLLECTION FOR UPDATES AND ONLY UPDATE THE CHANGED RECORDS NOT THE WHOLE UPDATE
--C. DELETION
--D. AUDIT TRAIL

        IF T_USERRECORD(I).USERID IS NULL THEN
        
          -- really do not want to delete here - will empty the collection each time
          -- T_USERRECORD_INSERT.DELETE;
          -- do not fetch from the cursor again; will be not find anything (without limit)
          -- FETCH CUR_USERSTRING BULK COLLECT
          --   INTO T_USERRECORD_INSERT;
          -- instead, copy record across, increasing collection size first
          T_USERRECORD_INSERT.EXTEND;
          T_USERRECORD_INSERT(T_USERRECORD_INSERT.COUNT) := T_USERRECORD(I);
        ELSE
        
          -- really do not want to delete here - will empty the collection each time
          -- T_USERRECORD_UPDATE.DELETE;
          -- do not fetch from the cursor again; will be not find anything (without limit)
          -- FETCH CUR_USERSTRING BULK COLLECT
          --  INTO T_USERRECORD_UPDATE;
          -- instead, copy record across, increasing collection size first
          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);
    
      FORALL M IN T_USERRECORD_UPDATE.FIRST .. T_USERRECORD_UPDATE.LAST
	 
	  --ONLY UPDATE THE CHANGED RECORDS NOT THE WHOLE UPDATE
        UPDATE QM_USERDATA
           SET USERNAME        = T_USERRECORD_UPDATE(M).USERNAME,
               DATE_OF_JOINING = T_USERRECORD_UPDATE(M).DATE_OF_JOINING,
               CREATED_BY      = T_USERRECORD_UPDATE(M).CREATED_BY,
               CREATION_DATE   = T_USERRECORD_UPDATE(M).CREATION_DATE,
               MODIFIED_BY     = T_USERRECORD_UPDATE(M).MODIFIED_BY,
               MODIFIED_DATE   = T_USERRECORD_UPDATE(M).MODIFIED_DATE
         WHERE USERID = T_USERRECORD_UPDATE(M).USERID;
      
    
    END LOOP;
    CLOSE CUR_USERSTRING;
    COMMIT;
    PC_STATUS := CONST_SUCCESS;
  
  EXCEPTION
    WHEN OTHERS THEN
      CLOSE CUR_USERSTRING;  
      PC_STATUS := CONST_FAILURE;
      
      PC_MESSAGE := 'ERROR IN FETCHING LIST' || ' ' || SQLCODE || ' ' || 
      SQLERRM || 'PR_CREATE_USERRECORD_STRING';
  
  END PR_CREATE_USERRECORD_STRING;

END PKG_USERRECORD;
/
select * from user_errors
--Use to display data
DECLARE
V_STRING VARCHAR2(5000);
V_STATUS CHAR(1);
V_MESSAGE VARCHAR2(5000);
BEGIN
PKG_USERRECORD.PR_CREATE_USERRECORD_STRING
('1~AKASH~01-AUG-22~5~01-AUG-22~4~04-AUG-22^2~AJAY~02-AUG-22~6~02-AUG-22~4~04-AUG-22^3~MEGHA~02-AUG-22~6~02-AUG-22~4~04-AUG-22',V_STATUS,V_MESSAGE);
DBMS_OUTPUT.PUT_LINE(V_STATUS||' '||V_MESSAGE);
END;
/
1 rows affected

dbms_output:
S 

--Use to display data
DECLARE
V_STRING VARCHAR2(5000);
V_STATUS CHAR(1);
V_MESSAGE VARCHAR2(5000);
BEGIN
PKG_USERRECORD.PR_CREATE_USERRECORD_STRING
('~AKASH~01-AUG-22~5~01-AUG-22~4~04-AUG-22^~AJAY~02-AUG-22~6~02-AUG-22~4~04-AUG-22^~MEGHA~02-AUG-22~6~02-AUG-22~4~04-AUG-22',V_STATUS,V_MESSAGE);
DBMS_OUTPUT.PUT_LINE(V_STATUS||' '||V_MESSAGE);
END;
/
1 rows affected

dbms_output:
S 

alter session set nls_date_format = 'YYYY-MM-DD'
SELECT * FROM QM_USERDATA;


USERID
USERNAME
DATE_OF_JOINING
CREATED_BY
CREATION_DATE
MODIFIED_BY
MODIFIED_DATE


42
AKASH
2022-08-01
5
2022-08-01
4
2022-08-04

43
AJAY
2022-08-02
6
2022-08-02
4
2022-08-04

44
MEGHA
2022-08-02
6
2022-08-02
4
2022-08-04
USERID	USERNAME	DATE_OF_JOINING	CREATED_BY	CREATION_DATE	MODIFIED_BY	MODIFIED_DATE
42	AKASH	2022-08-01	5	2022-08-01	4	2022-08-04
43	AJAY	2022-08-02	6	2022-08-02	4	2022-08-04
44	MEGHA	2022-08-02	6	2022-08-02	4	2022-08-04