Home > Enterprise >  Bulk collection in oracle
Bulk collection in oracle

Time:09-06

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;

CodePudding user response:

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.

  • Related