Collections in PL/SQL

Hello List,
Has anyone ever tried to pass in a list (a comma-delimited string) as a parameter to a pl/sql procedure or function and then use it in an 'in' clause of a select statement? Here's an abbreviated version of the code:
* Signature:

• function getServerEvents(eventTypes in varchar2) return serverEventsCurType;
• eventTypes looks like this when it's passed in ('b,v,r') or ('b,v') or ('b').

So I take the eventTypes param and run it against a split function that another developer in our org wrote (works like split in Perl) and get a pl/sql table back. I then loop through the pl/sql table and add single quotes around all of the elements ('b','v','r'). I then join them back together with a join function that returns a varchar2, comma delimited. I then try to use this joined, comma delimited variable (joinResults) in an 'in' clause of a select in a ref cursor. Weird, but I thought theoretically it would work. Problem is it doesn't at all and I have yet to find where you can pass in variables that are comma delimited like this in an 'in' clause of a select.

So, in order to solve the problem (as I"m on a deadline), I began to look at varrays and nested tables. I first created a table type in the database (which I don't like at all that you can't create these in pl/sql and that you physically have to create something in the database). I split my incoming parameter like above, but instead of joining it, I add each element of the split to a the table type using this kind of syntax : eventId tableType := tableType();
-- while looping, assign the split results to the table eventId.extend;
eventId(i) := splitResults(i);

I then use the table in the 'in' clause of the select like so: select * from myTable
where event_id in

	(select column_value from 
		table(cast(eventId as tableType)));

Okay, so when I run this from SQL*Plus through a test procedure it works (yeah! cause it seems really convoluted to just handle 1 list of items!). But I get the following error when we run it from Java: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PORTALACCOUNT.PORTALACCOUNTPKG", line 273 ORA-06512: at line 1

I know this is a lot of questions for one email, but a) is there a better way to handle this and b) if this is the best or only way, how do I handle the string buffer overflow?

Thanks for the input!

SE Teague
Aresenal Digital Solutions Received on Fri Jan 19 2001 - 08:48:03 CST