| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Collections in PL/SQL
If you are not depending on an index on the column try
where ','||eventTypes||',' like '%,'||event_id||',%'
Brian Norrell
Senior Software Engineer
QuadraMed
972-831-6600
-----Original Message-----
From: Susan E Teague [mailto:Susan_at_fileFRENZY.com]
Sent: Friday, January 19, 2001 8:51 AM
To: Multiple recipients of list ORACLE-L
Subject: 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
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-LReceived on Fri Jan 19 2001 - 09:34:52 CST
(or the name of mailing list you want to be removed from). You may
 |
 |