I have bulk collected data in T_USERRECORD through Cursor. Now inside T_USERRECORD loop I have to populate a separate collection for insert and update according to the commented section below, and have to make a bulk insert and update outside the loop. I am getting error(Reference to uninitialized collection) in this.
CREATE OR REPLACE PACKAGE PKG_USERRECORD IS
PROCEDURE PR_CREATE_USERRECORD_STRING(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('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', '[^^]+', 1, LEVEL) USER_INPUT
FROM DUAL
CONNECT BY LEVEL <= LENGTH('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') -
LENGTH(REPLACE('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', '^')) + 1
);
TYPE T_CUR_USERSTRING IS TABLE OF CUR_USERSTRING%ROWTYPE;
T_USERRECORD T_CUR_USERSTRING;
T_USERRECORD_INSERT T_CUR_USERSTRING;--Have to populate bulk collection for insert inside T_USERRECORD(it is giving error, collection not initialised)
T_USERRECORD_UPDATE T_CUR_USERSTRING;--Have to populate bulk collection for update inside T_USERRECORD(it is giving error, collection not initialised)
BEGIN
OPEN CUR_USERSTRING;
LOOP
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
FETCH CUR_USERSTRING BULK COLLECT
INTO T_USERRECORD_INSERT;
ELSE
FETCH CUR_USERSTRING BULK COLLECT
INTO T_USERRECORD_UPDATE;
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;
/
--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;
As the error says, you need to initialise your collections - for insert and update, not necessary for the main collection - either as they are declared:
...
T_USERRECORD_INSERT T_CUR_USERSTRING := T_CUR_USERSTRING();
T_USERRECORD_UPDATE T_CUR_USERSTRING := T_CUR_USERSTRING();
BEGIN
...
or in the main body:
...
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();
...
Then when you have decided whether to handle as an insert or update, do not delete all the existing entries from the relevant collection, and don't re-fetch into those collections - now you have removed the limit the fetch won't find anything, and if the limit was there it would fetch all the records after the one you are looking at. Instead, copy the current record to the collection:
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;
db<>fiddle with some additional comments, matching spec and body procedure declaration, dummy table, and using the passed-in value for the regex split; and showing S for both an update and an insert scenario.
There are other areas you could look at, including whether you really need to PL/SQL and loops/collections etc. for this at all - it could be done with a single MERGE - but that's rather beyond the scope of your current issue.