Re: Collections in PL/SQL

Hi Susan,

    I'm not sure that i had understand your question, cause my english isn't good enough, but if you want search a string into a varchar2, comma delimited, here you have a sample code

If we have a varchar2 variable called EVENT_ID, wich value is '1' and another varchar2 called SPLIT_RESULTS wich value is like ('1','2','3','4'), joined in a varchar2

# then the select will be
select * from myTable

Where instr(event_id,split_results) != 0

Sorry if this doesn`t solve tour question.

Susan E Teague wrote:

> 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
> Data Services
> susan_at_filefrenzy.com
> 919.760.1167
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Susan E Teague
> INET: Susan_at_fileFRENZY.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Fri Jan 19 2001 - 10:08:28 CST