RE: is it possible to use a function to

From: Duret, Kathy <kduret_at_starkinvestments.com>
Date: Fri, 25 Feb 2005 08:02:14 -0600
Message-ID: <5EAB052DE9A3BD4D84E6AE8EE3CCA7E5B7F012@sneetch.ad.starkinvestments.com>

1. The reason I need a function or a procedure is that the ('IBM') can = change. Needs to be a p_symbol variable
2. We will call this function from a number of different statements.

I am looking into Jacques "Ask Tom" link to see if this will work.

Thanks,

Kathy

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Knight, Jon
Sent: Thursday, February 24, 2005 5:39 PM To: Duret, Kathy; Oracle L (E-mail)
Subject: RE: is it possible to use a function to

For this kind of thing, I usually build the whole statement and = concatenate
my list into it.

But, I think you can do it without the function. Something like ...

select *

from test2                     - this statement can vary=20

where symbol in=20

(
  select distinct
    symbol
  from test
  where ...
)
/

Jon Knight

 -----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]

On Behalf Of Duret, Kathy

Sent:	Thursday, February 24, 2005 5:14 PM
To:	Oracle L (E-mail)
Subject:	is it possible to use a function to

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);=20

One symbol comming out of the function works fine. multiples do not the select test_function('IBM') from dual works just fine.

I cannot get the outer query to work.

What am I missing?

Thanks,

Kathy=20

CREATE OR REPLACE FUNCTION test_function ( p_symbol in test.symbol%type

         ) RETURN varchar2
is

  v_cur_symbol   alias.symbol%type;
  v_in_list_sql  varchar2 (4000);
  v_symbol       alias.symbol%type :=3D upper(p_symbol);

 =20
  cursor symbol_cur is=20

	select distinct a1.symbol  =20
	from   test=20
	where symbol =3D v_symbol
	                     )
            =20
BEGIN
     =20

   For symbol_recs in symbol_cur
   LOOP
      =20
   v_cur_symbol :=3D symbol_recs.symbol || ',';   =20
   v_in_list_sql :=3D rtrim(v_in_list_sql,' ') || v_cur_symbol;   =20
    =20
   END LOOP;    /* take off last comma */
   v_in_list_sql :=3D substr(rtrim(v_in_list_sql,' ') ,1,(length(rtrim(v_in_list_sql,' ')))-1 ) ;

   return v_in_list_sql;
  =20
  EXCEPTION
   WHEN No_Data_Found
   THEN

      return v_in_list_sql;
     =20

   WHEN OTHERS
   THEN

      raise_application_error(sqlcode, SUBSTR(SQLERRM,1,100));=20
             =20

   end;

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
--
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-l

Received on Fri Feb 25 2005 - 09:05:20 CST