Thanks to one of my customers, I am dealing with csv data in clob lately a lot. so they asked me if I can get a csv data in a clob column as separated columns using select statement. They wanted to show this data on their applicaitons. I suggested different methods but they said this won’t be used much just once or twice on a month. so I created this code and wanted to share with you:
I am using a pipe lined function in this solution. I created a type to define columns with 80 columns (c1 to c80) then my pipe lined function gets the clob value and parse it to first “lines” then “columns”. On this problem, we have some strict rules like every line separated by new line (chr(10)) and every column separated by a comma and every column surrounded by double quotes. So I made them hard coded (if I find time I will make them dynamic).
I also used a small dynamic sql to assign column values because I am lazy and didn’t want to write 80 rows like “v_line.c1 := regexp_substr….”
In addition, I created a “constructor function” to my type because I want to use this:
” v_line t_csv_to_table_row := t_csv_to_table_row();”
if I didn’t create a constructor function then I have to add 80 NULL at the statement about like this:
” v_line t_csv_to_table_row := t_csv_to_table_row(null,null,null,……..null);”
so result is:
as you see my query will always return 80 columns and columns data will be assigned from csv data. if there is no enough column in csv then remaining columns will be null.
using a polymorphic function could be better option on this but since I had limited time I produced a quick solution.
hope it helps.