CSV Data in Clob to Columns Using Select – Keep Learning Keep Living

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:

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;
/

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:

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));

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.

Leave a Reply Cancel reply