RE: is it possible to use a function to

  • From: "Duret, Kathy" <kduret@xxxxxxxxxxxxxxxxxxxx>
  • To: "Oracle L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 25 Feb 2005 08:02:14 -0600

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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.



--
//www.freelists.org/webpage/oracle-l
--
//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

Other related posts: