clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591652 fiddles created (45709 in the last week).

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


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"');
1 rows affected
 hidden batch(es)


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"');
1 rows affected
 hidden batch(es)


with dat(id,data) as (select id,data from disp_data) MERGE INTO push_data_temp tgt USING (select * FROM (SELECT id , LEVEL lvl , REPLACE(regexp_substr(data, '("[^"]*"|[^, "]+)', 1, level, null, 1),'""','') result FROM disp_data 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)) src ON (src.id = tgt.pid) WHEN MATCHED THEN UPDATE SET col1 = src."1_COL" , col2 = src."2_COL" , col3 = src."3_COL" , col4 = src."4_COL" , col5 = src."5_COL" , col6 = src."6_COL" , col7 = src."7_COL" , col8 = src."8_COL" , col8 = src."8_COL" , col9 = src."9_COL" , col10 = src."10_COL" , col11 = src."11_COL" WHEN NOT MATCHED THEN INSERT (pid, col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11) VALUES (src.id, src."1_COL", src."2_COL",src."3_COL",src."4_COL",src."5_COL", src."6_COL",src."7_COL",src."8_COL",src."9_COL",src."10_COL",src."11_COL")
ORA-00928: missing SELECT keyword
 hidden batch(es)


CREATE OR REPLACE PROCEDURE PUSH_DATA(id INTEGER, DAT VARCHAR2) IS BEGIN WITH dat(id, DATA) AS (SELECT id,data from disp_data) 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)) src ON (src.id = tgt.pid) WHEN MATCHED THEN UPDATE SET col1 = src."1_COL" , col2 = src."2_COL" , col3 = src."3_COL" , col4 = src."4_COL" , col5 = src."5_COL" , col6 = src."6_COL" , col7 = src."7_COL" , col8 = src."8_COL" , col8 = src."8_COL" , col9 = src."9_COL" , col10 = src."10_COL" , col11 = src."11_COL" WHEN NOT MATCHED THEN INSERT (pid, col1, col2,col3,col4,col5,col6,col7,col8,col9,col10,col11) VALUES (src.id, src."1_COL", src."2_COL",src."3_COL",src."4_COL",src."5_COL", src."6_COL",src."7_COL",src."8_COL",src."9_COL",src."10_COL",src."11_COL"); END;
ORA-24344: success with compilation error
 hidden batch(es)


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 id, lvl, result from rcte ) pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));
ID 1_COL 2_COL 3_COL 4_COL 5_COL 6_COL 7_COL 8_COL 9_COL 10_COL 11_COL
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"
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"
 hidden batch(es)


create table push_data_temp (pid number,col1 varchar2(100),col2 varchar2(300),col3 varchar2(100),col4 varchar2(100),col5 varchar2(100),col6 varchar2(100),col7 varchar2(100),col8 varchar2(100),col9 varchar2(100),col10 varchar2(100),col11 varchar2(100));
 hidden batch(es)


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 id, lvl, result from rcte ) pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));
2 rows affected
 hidden batch(es)


select pid,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11 from push_data_temp;
PID COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10 COL11
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"
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"
 hidden batch(es)