Hi,
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:
|
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 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
drop function Sf_Csv_To_Table; drop type t_csv_to_table_arr ; drop type t_csv_to_table_row; create type t_csv_to_table_row is object ( c1 varchar2(1500), c2 varchar2(1500), c3 varchar2(1500), c4 varchar2(1500), c5 varchar2(1500), c6 varchar2(1500), c7 varchar2(1500), c8 varchar2(1500), c9 varchar2(1500), c10 varchar2(1500), c11 varchar2(1500), c12 varchar2(1500), c13 varchar2(1500), c14 varchar2(1500), c15 varchar2(1500), c16 varchar2(1500), c17 varchar2(1500), c18 varchar2(1500), c19 varchar2(1500), c20 varchar2(1500), c21 varchar2(1500), c22 varchar2(1500), c23 varchar2(1500), c24 varchar2(1500), c25 varchar2(1500), c26 varchar2(1500), c27 varchar2(1500), c28 varchar2(1500), c29 varchar2(1500), c30 varchar2(1500), c31 varchar2(1500), c32 varchar2(1500), c33 varchar2(1500), c34 varchar2(1500), c35 varchar2(1500), c36 varchar2(1500), c37 varchar2(1500), c38 varchar2(1500), c39 varchar2(1500), c40 varchar2(1500), c41 varchar2(1500), c42 varchar2(1500), c43 varchar2(1500), c44 varchar2(1500), c45 varchar2(1500), c46 varchar2(1500), c47 varchar2(1500), c48 varchar2(1500), c49 varchar2(1500), c50 varchar2(1500), c51 varchar2(1500), c52 varchar2(1500), c53 varchar2(1500), c54 varchar2(1500), c55 varchar2(1500), c56 varchar2(1500), c57 varchar2(1500), c58 varchar2(1500), c59 varchar2(1500), c60 varchar2(1500), c61 varchar2(1500), c62 varchar2(1500), c63 varchar2(1500), c64 varchar2(1500), c65 varchar2(1500), c66 varchar2(1500), c67 varchar2(1500), c68 varchar2(1500), c69 varchar2(1500), c70 varchar2(1500), c71 varchar2(1500), c72 varchar2(1500), c73 varchar2(1500), c74 varchar2(1500), c75 varchar2(1500), c76 varchar2(1500), c77 varchar2(1500), c78 varchar2(1500), c79 varchar2(1500), c80 varchar2(1500), CONSTRUCTOR FUNCTION t_csv_to_table_row(SELF IN OUT NOCOPY t_csv_to_table_row) return self as result ); / Create Type Body t_csv_to_table_row as CONSTRUCTOR FUNCTION t_csv_to_table_row(SELF IN OUT NOCOPY t_csv_to_table_row) return self as result is begin return; end; End; / create type t_csv_to_table_arr is table of t_csv_to_table_row ; / create or replace function Sf_Csv_To_Table(p_clob clob) return t_csv_to_table_arr pipelined as v_size number; v_start_pos number := 1; v_new_position number := 0; v_string_line Varchar2(32767); v_line t_csv_to_table_row ; v_line_separator varchar2(10) := chr(10); v_clob clob := p_clob ; v_column_data_assignment clob; v_column_script Clob := ' declare V_String_Line varchar2(32767) := :StringLine; v_line t_csv_to_table_row := t_csv_to_table_row(); begin <<columns>> :OutRow := v_line; end;'; Procedure IP_Init_Column_Script as Begin For i in 1..80 Loop v_column_data_assignment := v_column_data_assignment || ' v_line.C'||to_char(i) ||' := Regexp_Substr(V_String_Line, ''"(.*?)"'',1,'||to_char(i)||',''i'',1);'; End Loop; v_column_script := Replace (v_column_script, '<<columns>>', v_column_data_assignment); End IP_Init_Column_Script; begin IP_Init_Column_Script; v_size := dbms_lob.getlength(v_clob); while v_start_pos <= v_size loop v_new_position := nvl(instr(v_clob, v_line_separator, v_start_pos), 4000); v_string_line := substr(v_clob, v_start_pos , v_new_position-v_start_pos ); V_Line := t_csv_to_table_row(); Execute immediate v_column_script using V_String_Line, out V_Line; Pipe Row(v_line); v_start_pos := v_new_position+length(v_line_separator); end loop; return; end; / |
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:
|
1 2 3 4 5 |
select * from Sf_Csv_To_Table('"row1", "1","test data"' || chr(10)|| '"row2", "2","test data2","new column on 2"' || chr(10)|| '"row3", "3","test data3","","more new column"' || chr(10)|| '"row4", "4","test data4","",""' || chr(10)); |
result:
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.