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" 

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 ) )
>  
> 
> 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.
> 
> then followed the ask Tom advice.  Weird, I looked there first and nothing 
> came up.... Glad
> someone could find it.
> 
> Tom, 
> 
> I am still going to try what you sent me this afternoon. I will post that if 
> I get it
> working.... 
> 
> Thanks to All.
> 
> The pipeline function would have been optimal... but I am on 8i.
> 
> Lots of good suggestions.
> 
> Have a great weekend EVERYONE... The virtual beers are on me!
> 
> Kathy
> 
> -----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
> 
> 
> 
> Ask Tom Kyte:
> varying elements in IN list
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:8321215185943122431::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:110612348061
> or
> http://tinyurl.com/4u6fc
> 
> -----Original Message-----
> Duret, Kathy
> 
> 8.1.7.4 Solaris 9
> What I want to do is build a In list and feed it to a query:
> 
> select * from test2 where
> symbol in ( select test_function('IBM') symbol from dual);
> 
> 
> 
> 
> 
> 
> 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.
> 
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 


=====
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"

------------------------------------------------------------


                
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - now with 250MB free storage. Learn more.
http://info.mail.yahoo.com/mail_250
--
http://www.freelists.org/webpage/oracle-l

Other related posts: