Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: IN CLAUSE PL/SQL

Re: IN CLAUSE PL/SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 19 Nov 1998 13:53:52 GMT
Message-ID: <36551c12.3535984@inet16.us.oracle.com>


On 19 Nov 1998 12:13:18 GMT, dnguyeb_at_osf1.gmu.edu (Dong Duong Nguyen) wrote:

>Hi,
> I'm trying to write a stored procedure that will
>use the procedure's parameter in the IN clause. Here is
>my create for the procedure:
>
>CREATE OR REPLACE PROCEDURE GetGoodService ( gs_cv IN OUT cursor_types.GoodServi
>ce, CODE IN VARCHAR2 ) AS
>BEGIN
>
> OPEN gs_cv FOR SELECT * FROM GOOD_AND_SERVICE WHERE GOOD_AND_SERVICE.GOOD_S
>ERVICE_KEY IN ( CODE );
>
>END GetGoodService
>
> CODE contains comma delimited parameters to the IN clause. This
>does not seem to work. Is there a way I can do this? I need to return
>a cursor so I can't use DYNAMIC SQL.

If you don't want to use Dynamic SQL then you can use the instr() function.

eg.

open gs_cv for
  select *
    from good_and_service
   where instr( ','||code||',', ','||good_service_key||',' ) > 0;

I enclose both sides with commas so as not to return bogus rows like if the column value is 'AT' and the code string looks like AT,CAT,BAT. This way only the column with 'AT' will match. No index used, but no Dynamic SQL either. As long as the code itself does not contain a comma, this will work.

hope this helps.

chris.

>
>Thanks
>Doug Nguyen

--
Chirstopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 19 1998 - 07:53:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US