I am using below query to split the given string in DISP_DATA tbale, data column and push it into destination table.
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)
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));
However i have requirement now to wrap it in pl/sql procedure and split & insert/update rows if already existing, all dynamically.
- 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.
- 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
- 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"');
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=0bfa3bb4c7cfe0eff3e2138e93072fac
Go to Source
Author: Vini