Re: passing IN parameter to stored procedure

earl 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!
You are inappropriately using the USER_GRPS parameter in your WHERE clause. Your problem has nothing to do with it being a stored procedure. Your syntax is flawed.

Go to www.psoug.org
click on Morgan's Library
click on Conditions
scroll down to the COMPLEX IN demo.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)