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!
It most certainly does get passed correctly. Your issue is using a string in place of a delimited list. You'll need to convert that string to a usable list before you can expect it to properly constrain your results. Simply putting it in a WHERE clause as-is won't work, as you've discovered. Also, this is not SQL Server; if you want a result set you NEED to select from a table:

select GetRoles('SUPPORT','OAK\a12345­,OAK\b12345') from dual;

You haven't provided the entire text of your GetRoles function (you've eliminated key elements such as the RETURN declaration and the rest of the code) so attempting to test this is wasted effort; I will not guess what you've written nor will I spend time trying to fix that which you could not provide in its entirety.

Your problem lies with using a string in place of a proper, delimited list. Your values ARE passed correctly; convert your string to a valid list and you'll see results.

David Fitzjarrell Received on Wed Aug 10 2005 - 10:04:33 CDT