sqloracle-databasecsvplsqlsubquery-factoring

Build table in WITH clause from csv

I have a csv file that was dumped from the table.
Now I want to build the table using WITH from that csv file.
I had tried by taking all the values of each column, separated by comma in separate variable and then inserting by doing union in WITH but that was not possible.
Is it possible by inserting into table with comma separated values without loop?
Let say
$roll_no = "1,2,3,4,5"
$name = "name1,name2,name3,name4,name5"
roll_no name
---------------
1 name1
2 name2
3 name3
4 name4
5 name5
Is there any other way to do it ? Thanks

Solution

  • Can pass your comma seperated string like in given below way. This would convert entire comma separated string in different rows.

     SELECT trim(x.column_value.extract('e/text()')) COLUMNS from t t, table    
(xmlsequence(xmltype('<e><e>' || replace(valuestring,--put comma seperated string here
':','</e><e>')||  
 '</e></e>').extract('e/e'))) x   );