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