[PL/SQL] Duplicate rows changing column values

Imagine you have a table user_accesses like this:

NAME--SURNAME--DEPARTMENT
John--Smith----Marketing;Sales
Mary--Ann------Accounting;Something;Something_else

And you want to split the department column so that each row only has one value instead of a semi-colon separated list.

The following script replicates the rows where the specified column has multiple values, replicating all the data in the other columns. The specified column will be populated with each value in the list (replicated values are not discarded).

NOTE: if you're using a primary key, add the logic to create a new primary key BEFORE the INSERT command. (See comments).

Before actually getting to the script we should prepare a LOG table and a LOG procedure to log any errors during our script execution:

LOG Table:

 CREATE TABLE MYLOG(  
 TS DATE,   
 STEP VARCHAR2(100 BYTE),   
 ERROR VARCHAR2(1000 BYTE),   
 RROWID VARCHAR2(2000 BYTE)  
 );  

It has some simple fields:

• TS: timestamp when the error occurred
• STEP: where did the error occur inside the script
• ERROR: the error that occurred
• RROWID: the ROWID we were working on when the error occurred

LOG Procedure (using PRAGMA AUTONOMOUS_TRANSACTION, we enclose the procedure in a separate transaction so that it cannot commit on the main transaction and is not rolled back from it either):

 CREATE OR REPLACE PROCEDURE MYLOGP(  
 p_step IN VARCHAR2,  
 p_error IN VARCHAR2,  
 p_rowid IN VARCHAR2   
 ) AS PRAGMA AUTONOMOUS_TRANSACTION;  
 BEGIN  
  INSERT INTO MYLOG VALUES(SYSDATE, p_step, p_error, p_rowid);  
  COMMIT;  
 END MYLOGP;  

Now we're ready to run our script:

 DECLARE  
 -- temp variable to store our list of values  
 TYPE varray_type_varchar2 IS TABLE OF VARCHAR2(1000);  
 list_val varray_type_varchar2 := varray_type_varchar2(); -- Initialize it!  
 l_string VARCHAR2(1000); -- temp to store the current column value in list format  
 l_explode_at PLS_INTEGER; -- temp indicating WHERE to split the list  
 l_row_to_insert MYTABLE%ROWTYPE; -- temp to prepare the new line to insert. CHANGE MYTABLE WITH YOURS  
 l_separator VARCHAR2(1):=';'; -- CHANGE IT WITH YOUR SEPARATOR  
 BEGIN  
  -- get all lines with the ROWID  
  FOR myrows IN(SELECT t.ROWID AS myrowid, t.MYCOLUMN -- CHANGE MYCOLUMN WITH YOURS  
         FROM MYTABLE t  
         )LOOP  
   l_string:= myrows.MYCOLUMN;  
   -- explode the column values on the separator. If it's not a list, skip the line  
   l_explode_at := INSTR (l_string, l_separator); -- if the separator is not in the string, it returns 0  
   IF l_explode_at != 0 THEN  
           -- split all the values in the list and store them in our temp variable  
    LOOP  
    EXIT WHEN l_string IS NULL; -- keep going as long as there are values in the list  
     l_explode_at := INSTR (l_string, l_separator);  
     list_val.EXTEND;  
     -- if we have other values after this one  
     IF l_explode_at !=0 THEN  
      -- store it and keep going  
      list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, l_explode_at - 1)); --get the current value  
      l_string := SUBSTR (l_string, l_explode_at + 1); -- and move on the the next one  
     -- if there are no more values after me  
     ELSE  
      -- store the last one and quit  
      list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, LENGTH(l_string)));  
      l_string := NULL;  
     END IF;  
    END LOOP;  
    -- for each value gathered  
    -- clean temp variables  
    l_row_to_insert:=NULL;  
    -- get the data from the row we're working on  
    SELECT * INTO l_row_to_insert  
    FROM MYTABLE  
    WHERE ROWID = myrows.myrowid;  
       -- for each value we split  
    FOR myindex IN list_val.FIRST .. list_val.LAST LOOP  
     BEGIN  
      -- clone the starting row, but change the value of MYCOLUMN with the one we split  
      -- prepare the new line  
      l_row_to_insert.MYCOLUMN:= list_val(myindex);  
            -- and insert it as new line  
            /* PRIMARY KEY WARNING - if you have a primary key, insert the logic to get a new one HERE  
            eg:  
            l_new_key:= MYSEQUENCE.NEXTVAL;  
            l_row_to_insert.MYKEY:= l_new_key;  
            */  
      INSERT INTO MYTABLE VALUES l_row_to_insert;  
      COMMIT;  
     EXCEPTION WHEN OTHERS THEN  
      mylogp('INSERT NEW LINE', SQLERRM, myrows.myrowid);  
     END;  
    END LOOP;  
    -- delete the old line where we started from  
    BEGIN  
     DELETE FROM MYTABLE  
     WHERE ROWID=myrows.myrowid;  
    EXCEPTION WHEN OTHERS THEN  
     mylogp('DELETE ORIGINAL ROW', SQLERRM, myrows.myrowid);  
    END;  
   END IF;  
   -- clean temp variables  
   l_string:=NULL;  
   l_explode_at:=NULL;  
   list_val:=varray_type_varchar2();  
  END LOOP;  
 END;  

It parses the specified column to check if there's a list of values inside, delimited by the specified delimiter. If that's the case, it proceeds to duplicate the row putting each value from the list as the new column value, and when it's done, it deletes the source row.

Some notes:

• change MYTABLE to your table. In our example it would be user_accesses
• change MYCOLUMN to your column. In our example it would be department
• change l_separator to your separator. In our example it would be ';'