Re: passing IN parameter to stored procedure

On 10 Aug 2005 07:32:06 -0700, "earl" <earlcoombs_at_gmail.com> wrote:

>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!
For the value of USER_GRPS use
'<value><terminator><value><terminator>...' and instead of
USERS.USERNAME IN USER_GRPS
use
USER_GRPS LIKE '%'||USER.USERNAME||'<terminator>%'

Jaap. Received on Thu Aug 11 2005 - 12:33:21 CDT