Oracle Procedure to dynamically insert and update comma separated string into dest table

Question

EDIT 1: I was able to compile the procedure suggested by @radagast81 but it just keeps running. Any help on this will be appreciated.

I had received solution to split comma separated string in my previous thread. So , I am using below query to split the given string into destination table.

PUSH_DATA_TEMP Cols: DISP_PK-> Primary key should be uniqe incrementing numbers per id. PID-> ID for every string col1........col11

insert into push_data_temp (pid,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12)
with rcte (id, data, lvl, result) as (
  select id, data, 1,
    regexp_substr(data, '("[^"]*"|[^, "]+)', 1, 1, null, 1) )
  from disp_data
  union all
  select id, data, lvl + 1,
   regexp_substr(data, '("[^"]*"|[^, "]+)', 1, lvl + 1, null, 1) 
  from rcte
  where lvl <= regexp_count(data, '("[^"]*"|[^, "]+)')
)
select *
from (
  select disp_pk,id, lvl, replace(result,'""','') as final
  from rcte
)
pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,12));

However i have additional requirement now to use pl/sql procedure and split & insert/update rows if already existing, all dynamically.

1. I need to make the columns above dynamic, so the string could get split in 11 columns as above or 15. Maximum number is 30.

So i need to write a procedure, that splits and pushes string into columns dynamically.

2. So it should insert rows and if the row already exists, update the columns, both dynamically. Probably with loops. So condition for inset and update for each row is mandatory. ID/PID is the id of the row.

DB-12C

3. For id_pk, it should be unique number generated incrementing by 1. ID/PID, needs to be passed majorly as parameter which identifies each string.

Sample data: The first string for PID=100 has string split into 11 columns and the second with PID=110 will split into 12 columns and so on.

create table disp_data(id number, data varchar2(4000));

Insert into disp_data(id,data) values(100,
'"Project title as per the outstanding Requirements","The values are, not with respect to the requirement and analysis done by the team. 
Also it is difficult to, prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","","","","","25"');

Insert into disp_data(id,data) values(110,
'"Project title afor BYU heads","The values are, exactly up to the requirement and analysis done by the team. 
Also it is difficult to, prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"');

Answer 1

You can basically use your existing SELECT and then simply put it into a MERGE-Statement:

WITH dat(id, DATA) AS (SELECT 110,'"Project title afor BYU heads","The values are, exactly up to the requirement and analysis done by the team. 
Also it is difficult to, prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"' FROM dual)
MERGE INTO push_data_temp tgt
USING (SELECT * 
         FROM (SELECT id
                    , LEVEL lvl
                    , REPLACE(regexp_substr(data, '("[^"]*"|[^, "]+)', 1, level, null, 1),'""','') result
                 FROM dat
              CONNECT BY regexp_substr(data, '("[^"]*"|[^, "]+)', 1, level, null, 1) IS NOT NULL)
       pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)) src
  ON (src.id = tgt.pid)
WHEN MATCHED THEN UPDATE
 SET col1 = src."1_COL"
   , ...
   , col30 = src."30_COL"
WHEN NOT MATCHED THEN INSERT (pid, col1, ..., col30)
VALUES (src.id, src."1_COL", ..., src."30_COL")

Wrapping this logic into a Procedure is also fairly easy:

CREATE OR REPLACE 
PROCEDURE PUSH_DATA(id INTEGER, DAT VARCHAR2) IS
BEGIN
  MERGE INTO push_data_temp tgt
  USING (SELECT * 
           FROM (SELECT id
                      , LEVEL lvl
                      , REPLACE(regexp_substr(dat, '("[^"]*"|[^, "]+)', 1, level, null, 1),'""','') result
                   FROM dual
                CONNECT BY regexp_substr(dat, '("[^"]*"|[^, "]+)', 1, level, null, 1) IS NOT NULL)
         pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)) src
    ON (src.id = tgt.pid)
  WHEN MATCHED THEN UPDATE
   SET col1 = src."1_COL"
     , ...
     , col30 = src."30_COL"
  WHEN NOT MATCHED THEN INSERT (pid, col1, ..., col30)
  VALUES (src.id, src."1_COL", ..., src."30_COL");
END;

You can then call it as following:

BEGIN
  PUSH_DATA(110,'"Project title afor BYU heads","The values are, exactly up to the requirement and analysis done by the team. 
Also it is difficult to, prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team","Active","Disabled","25 tonnes of fuel","www.examplesites.com/html.asp&net;","Apprehension","","","","25","Stable"');
END;