Oracle comma separated parameter to row and join with other tables

I am using Oracle 10g. My Scenario:

I am getting some more than 4000 records in a comma separated string('ord0000,ord0001开发者_如何学C,ord0002,......') as a parameter. I need to compare these values against a table1 and find out the matching recordset.

For that purpose I created a function as below:

function get_split_values (

csv_string varchar2
) return split_table pipelined
as
Delimit_String varchar2(32767) := csv_string;
Delimit_index integer;
begin
loop
Delimit_index := instr(delimit_string,',');
if Delimit_index > 0 then
pipe row(substr(delimit_string,1,delimit_index-1));
delimit_string := substr(delimit_string,delimit_index+1);
else
pipe row(delimit_string);
exit;
end if;
end loop;
return;
end get_split_values;

Now when I used this function to join with my table1 in a procedure as below:

create procedure abc (parameter_csv varchar2,...)
as
begin
open cursor for
select t.col1 from table1 t join table(get_split_values(parameter_csv)) x
on x.column_value = t.col1;
...
end abc;

It works fine when the parameter_csv have some around 300 or 400 IDs like('ord0000,ord0001,ord0002,......') but when it contains more that records I got the error "ORA 01460 : unimplemented or unreasonable conversion requested."

I don't understand what raise this error. Any ideas? OR is there any best way to accomplish this task.

---

Initially I thought you were overflowing your varchar2(32767) but a quick look at your sample IDs indicates that you shouldn't be maxing out that early(400 ids).

A quick google of the error led me to this forum in OTN: http://forums.oracle.com/forums/thread.jspa?threadID=507725&start=15&tstart=0

And to this blog post: http://oraclequirks.blogspot.com/2008/10/ora-01460-unimplemented-or-unreasonable.html

which indicates that this might be an oracle bug

---

If its a bug with using the PL/SQL procedure, you can just split the string as part of an inline view. Something like.

SELECT T.col1
FROM   table1 T
JOIN   (   SELECT  REGEXP_SUBSTR( parameter_csv, '[^,]+', 1, LEVEL )  AS id
           FROM    DUAL
           CONNECT BY LEVEL <=
                LENGTH( REGEXP_REPLACE( parameter_csv, '[^,]+', '' ) ) + 1
       ) X
ON     X.id = T.col1;

NOTE: Doesn't handle things like duplicate IDs in the the csv, empty values in the csv ,, etc