Parsing CSV Lines and columns in PLSQL – Keep Learning Keep Living

Hi,

Yesterday, one of my customers asked for help to tune a procedure and when I investigated the problem I find out that their method to get a specific “column” value in csv file was the problem. So we discuss the situation and adopt a new version of the code. While doing that I just create a pipe lined function to get same data just for fun and wanted to share it here.

So let’s say have a CSV (comma separated values) file and you need to parse it line by line and maybe even you need to get the “columns” (and by saying columns I meant every comma separated values in a line of csv file) row by row. To achieve that I created a pipe lined function, here it is:

create or replace function sf_parse(p_clob clob, p_separator varchar2) return sys.ODCIVARCHAR2LIST pipelined as
  v_size number;
  v_start_pos number := 1;
  v_new_position number := 0;
  v_line varchar2(4000);
  x_clob clob := p_clob || to_clob(p_separator);
begin
  v_size := dbms_lob.getlength(x_clob);
  
  while v_start_pos <= v_size loop
    
    v_new_position := nvl(instr(x_clob, p_separator, v_start_pos), 4000);
    v_line := substr(x_clob, v_start_pos , v_new_position-v_start_pos );
    v_start_pos := v_new_position+length(p_separator);

    pipe row(v_line);
  end loop;
  
  return;
end;
/

create or replace function sf_parse(p_clob clob, p_separator varchar2) return sys.ODCIVARCHAR2LIST pipelined as

v_size number;

v_start_pos number := 1;

v_new_position number := 0;

v_line varchar2(4000);

x_clob clob := p_clob || to_clob(p_separator);

begin

v_size := dbms_lob.getlength(x_clob);

while v_start_pos <= v_size loop

v_new_position := nvl(instr(x_clob, p_separator, v_start_pos), 4000);

v_line := substr(x_clob, v_start_pos , v_new_position-v_start_pos );

v_start_pos := v_new_position+length(p_separator);

pipe row(v_line);

end loop;

return;

end;

this function gets csv data as clob and parse to lines by a separator. Definition of Line is quite simple but you must be careful the platform. On Windows OS every line is ended with a carriage return (chr(13)) and a new line character (chr(10)) but on Linux OS it is just a new line character so if your CSV file is created on Windows then you must use chr(13) || chr(10) as new line (or enter) character and on Linux just chr(10).

create table tmp_clob (id number, myclob clob);

insert into tmp_clob values 
(1,
  to_clob('1,my first line,some data,123') || to_clob(chr(10)) ||
  to_clob('2,my second line,some more data,456') || to_clob(chr(10)) ||
  to_clob('3,my third line,some much more data,789') 
);
commit;

select *
from sf_parse((select myclob from tmp_clob where id = 1), chr(10));
---------------------------------------------------------------------
1,my first line,some data,123
2,my second line,some more data,456
3,my third line,some much more data,789

create table tmp_clob (id number, myclob clob);

insert into tmp_clob values

(1,

to_clob('1,my first line,some data,123') || to_clob(chr(10)) ||

to_clob('2,my second line,some more data,456') || to_clob(chr(10)) ||

to_clob('3,my third line,some much more data,789')

);

commit;

select *

from sf_parse((select myclob from tmp_clob where id = 1), chr(10));

---------------------------------------------------------------------

1,my first line,some data,123

2,my second line,some more data,456

3,my third line,some much more data,789

as you see we have all three lines. we can even take this further and use one of the lines as a input parameter of sf_parse:

select *
from sf_parse('1,my first line,some data,123', ',');
-------------------------------------------------------
1
my first line
some data
123

select *

from sf_parse('1,my first line,some data,123', ',');

-------------------------------------------------------

my first line

some data

123

we have all “columns” in csv as rows for the first line. you don’t have copy and paste the line info of course:

select *
from   sf_parse((select myclob from tmp_clob where id = 1), chr(10)) MyLines,
       sf_parse(MyLines.Column_value, ',');
----------------------------------------------------
Column_value                                    Column_value_1
1,my first line,some data,123                   1
1,my first line,some data,123                   my first line
1,my first line,some data,123                   some data
1,my first line,some data,123                   123
2,my second line,some more data,456             2
2,my second line,some more data,456             my second line
2,my second line,some more data,456             some more data
2,my second line,some more data,456             456
3,my third line,some much more data,789         3
3,my third line,some much more data,789         my third line
3,my third line,some much more data,789         some much more data
3,my third line,some much more data,789         789

select *

from sf_parse((select myclob from tmp_clob where id = 1), chr(10)) MyLines,

sf_parse(MyLines.Column_value, ',');

----------------------------------------------------

Column_value Column_value_1

1,my first line,some data,123 1

1,my first line,some data,123 my first line

1,my first line,some data,123 some data

1,my first line,some data,123 123

2,my second line,some more data,456 2

2,my second line,some more data,456 my second line

2,my second line,some more data,456 some more data

2,my second line,some more data,456 456

3,my third line,some much more data,789 3

3,my third line,some much more data,789 my third line

3,my third line,some much more data,789 some much more data

3,my third line,some much more data,789 789

have a nice day.

PS: I still can not believe that we have to talk/protest about “racism” in 2020 and I am deeply sorry for days. Life matters. We always tend to believe that we are the superior creature but we can not even understand the importance of life. Remember George Floyd. Peace.

Leave a Reply Cancel reply