| 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: is it possible to use a function to... SOLUTION 1
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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
> 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_at_quest.com]
> 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
> --
> http://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_at_yahoo.com
"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
-- http://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. -- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 28 2005 - 09:36:14 CST
 |
 |