Re: passing IN parameter to stored procedure

From: Matthias Kleinicke <Matthias.Kleinicke_at_gmx.de>
Date: Wed, 10 Aug 2005 23:08:48 +0200
Message-ID: <dddpsn$kp3$1@online.de>

Hi,

earl schrieb:
> Environment: Oracle 10g (10.0.1.4), MS Windows Server 2003.
> I can not get my stored procedure to process an "IN" clause passed
> parameter:

It is processed, but is is of type VARCHAR2, so it's one value!
> CURSOR c1 IS
> SELECT ROLEID
> FROM USERS
> WHERE USERS.APPNAME = APP_NAME AND
> USERS.USERNAME IN USER_GRPS;

Here You could use dynamic SQL:

   ...
   type t_cursor is ref cursor;
   v_cursor t_cursor;
begin

   open v_cursor for 'select ... where ... in ('||user_grps||')';    fetch v_cursor into ...;
   while v_cursor%found loop

     ...
     fetch ...

   end loop;
   close v_cursor;
   ...
end;
If doin this don't forget to pass the values quoted: '''OAK\a12345'',''OAK\b1234'''.

HTH Received on Wed Aug 10 2005 - 16:08:48 CDT