passing IN parameter to stored procedure

From: earl <earlcoombs_at_gmail.com>
Date: 10 Aug 2005 07:32:06 -0700
Message-ID: <1123684326.796570.290940@o13g2000cwo.googlegroups.com>

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:

create or replace function GetRoles(APP_NAME IN VARCHAR2, USER_GRPS IN VARCHAR2) <USER_GRPS needs to be a string of characters to be used in the WHERE IN clause of a cursor SELECT>

   v_Roles varchar2(100);

   CURSOR c1 IS

      SELECT ROLEID
         FROM USERS
         WHERE USERS.APPNAME = APP_NAME AND
         USERS.USERNAME IN USER_GRPS;

BEGIN
  OPEN c1;
  LOOP

     FETCH c1 INTO v_Roles;
     EXIT WHEN c1%NOTFOUND;

WHEN TESTING WITH: select GetRoles('SUPPORT','OAK\a12345­,OAK\b12345');

it never succeeds - the second string does not get passed correctly.

HELP! Received on Wed Aug 10 2005 - 09:32:06 CDT