How to covert comma separated values into a table in Oracle?

One of my friends was working on production support for a system and was dealing with a tidious process of manually verifying thousands of rows to filter out certain records. The process was error prone and demanded lot of concentration all the time. She was looking for a script to make it better.

The requirement: She would be given a set of data (comma separated values) and she need to verify if that data is present in database or not. If she is given 1000 numbers but only 600 exist in database, she has to figure out the missing 400 and send them back for some action.

She wanted to use a join (left outer) on the table in DB and the given list to get the missing records, but the list she got is a comma delimted list and she was looking for a way to convert it into table. I gave the following script (after googling and some research) which did the trick for her..

Step1: Create type

create or replace type split_tbl as table of varchar2(32767);
/
show errors;

Step2: Create function

create or replace function splitfunc

(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;

l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;

end splitfunc;
/
show errors;

Step2: The Query

select a.column_value from table(SPLITFUNC('143,3455,356')) a
left outer join TBL_MyTable b on a.column_value = b.TBL_pk
where b.TBL_pk is null;

As you can see, this script creates a type and then a user defined function which uses the type. In the query, we use the function to make the list as table and then use the returned table in join to filter the data.

If you are wondering what that type does, check this out. Also you can know more about the Pipelined table functions here.

Let me know if this helped you and also if you have a better method to do this..