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();
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 |