Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: IN CLAUSE PL/SQL
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.