can't because I won't know ahead of time how many objects can come = back... could be one.... could be many. Kathy -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Connor McDonald Sent: Friday, February 25, 2005 8:29 PM To: Duret, Kathy; Oracle L (E-mail) Subject: RE: is it possible to use a function to... SOLUTION 1 You might want to consider the CARDINALITY hint so you can let the = optimizer know how many objects are in your "in-list"=20 hth connor --- "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx> wrote: > select * from alias > where symbol in ( select * > from THE ( select cast( symbol_in_list(ALL_SYMBOLS_FOR('FORA-NA' )) as = symboltableType ) from > dual ) ) > =20 >=20 > Ok. This works. I created a function called all_symbols_for which = passes in the symbol and > passes out all the values in varchar2 that I need. >=20 > then followed the ask Tom advice. Weird, I looked there first and = nothing came up.... Glad > someone could find it. >=20 > Tom,=20 >=20 > I am still going to try what you sent me this afternoon. I will post = that if I get it > working....=20 >=20 > Thanks to All. >=20 > The pipeline function would have been optimal... but I am on 8i. >=20 > Lots of good suggestions. >=20 > Have a great weekend EVERYONE... The virtual beers are on me! >=20 > Kathy >=20 > -----Original Message----- > From: Jacques Kilchoer [mailto:Jacques.Kilchoer@xxxxxxxxx] > Sent: Thursday, February 24, 2005 5:44 PM > To: Duret, Kathy; Oracle L (E-mail) > Subject: RE: is it possible to use a function to >=20 >=20 >=20 > Ask Tom Kyte: > varying elements in IN list > http://asktom.oracle.com/pls/ask/f?p=3D4950:8:8321215185943122431::NO::F4= 950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061 > or > http://tinyurl.com/4u6fc >=20 > -----Original Message----- > Duret, Kathy >=20 > 8.1.7.4 Solaris 9 > What I want to do is build a In list and feed it to a query: >=20 > select * from test2 where > symbol in ( select test_function('IBM') symbol from dual); >=20 >=20 >=20 >=20 >=20 >=20 > This transmission contains information solely for intended recipient = and may be privileged, > confidential and/or otherwise protect from disclosure. If you are not = the intended recipient, > please contact the sender and delete all copies of this transmission. = This message and/or the > materials contained herein are not an offer to sell, or a solicitation = of an offer to buy, any > securities or other instruments. The information has been obtained or = derived from sources > believed by us to be reliable, but we do not represent that it is = accurate or complete. Any > opinions or estimates contained in this information constitute our = judgment as of this date and > are subject to change without notice. Any information you share with = us will be used in the > operation of our business, and we do not request and do not want any = material, nonpublic > information. Absent an express prior written agreement, we are not = agreeing to treat any > information confidentially and will use any and all informati > on and reserve the right to publish or disclose any information you = share with us. >=20 >=20 >=20 > -- > //www.freelists.org/webpage/oracle-l >=20 =3D=3D=3D=3D=3D Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" Co-author: "Oracle Insight - Tales of the OakTable" web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald@xxxxxxxxx "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, = and...he will sit in a boat and drink beer all day" ------------------------------------------------------------ =09 __________________________________=20 Do you Yahoo!?=20 Yahoo! Mail - now with 250MB free storage. Learn more. http://info.mail.yahoo.com/mail_250 -- //www.freelists.org/webpage/oracle-l This transmission contains information solely for intended recipient and = may be privileged, confidential and/or otherwise protect from = disclosure. If you are not the intended recipient, please contact the = sender and delete all copies of this transmission. This message and/or = the materials contained herein are not an offer to sell, or a = solicitation of an offer to buy, any securities or other instruments. = The information has been obtained or derived from sources believed by us = to be reliable, but we do not represent that it is accurate or complete. = Any opinions or estimates contained in this information constitute our = judgment as of this date and are subject to change without notice. Any = information you share with us will be used in the operation of our = business, and we do not request and do not want any material, nonpublic = information. Absent an express prior written agreement, we are not = agreeing to treat any information confidentially and will use any and = all information and reserve the right to publish or disclose any = information you share with us. -- //www.freelists.org/webpage/oracle-l