Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

CSV Data in Clob to Columns Using Select

Mustafa, 2020-07-142020-07-14

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:

Oracle PL/SQL
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:

Oracle PL/SQL
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.

12c 18c 19c Development Useful Scripts csv to columnsparse csv into columns in sqlselect csvsql scriptsuseful scripts

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes