Re: Pass a variable into procedure to use as column in select statement

From: <sybrandb_at_hccnet.nl>
Date: Thu, 03 Apr 2008 19:35:34 +0200
Message-ID: <hr4av3t5nbtqp5m7vkgaigca613fcirvkf@4ax.com>

On Thu, 3 Apr 2008 09:57:38 -0700 (PDT), OutCast <Joe.r.rhea_at_jpmchase.com> wrote:

>I'm new to PL/SQL and having trouble passing a variable into a
>procedure and using it as a column identifier in a select statement.
>
>I'm using this as a universal procedure to pass access information to
>applications based on user authorization.
>I have a table which includes all users with a column for all
>applications. I simple 'Y' or 'N' indicates which applications the
>user can access.
>I want to pass in the application name (This will as be the column
>name) and through a simple select statement needed access
>information.
>I know that I can not use a bind variable as an identifier (as my
>example attempts), but how or can I accomplish this another way.
>I really don't want to write 15 different select statements for each
>application, plus don't want to change the procedure every time I need
>to add a new application (column) to the user table.
>
>He is an example of what I'm trying to do:
>
>Procedure Schema_Access
>(i_User_ID varchar,i_APP_ID varchar, o_User_ID out varchar, o_Password
>out varchar)
>IS
>v_Access varchar(1);
>BEGIN
> select i_APP_ID into v_Access from User_Main where User_ID =
>UPPER(i_User_ID);
> If v_Access = 'Y' then
> o_User_ID := 'UserName';
> o_Password := 'Password';
> else
> o_User_ID := i_User_ID;
> o_Password := 'No Access';
> end if;
>
> If the are better ways to accomplish the same task, please let me
>know.
>
>Thanks in advance

The design of your user_main table is denormalized and consequently incorrect.
This is why you need to resort to kludges you try to describe above. What you should have done is

• create a table with users, pk is user_id
• create a table with applications, pk is app_id
• create a table mapping users to applications. This implements a n:m relationship (one application has multiple users, and one user has multiple applications) and is also called a junction table.

To check access for the user simply

check_access(p_user in varchar2, p_app in varchar2, p_flag out number) is
 flag number;
dummy varchar2(1);
begin
select 'x
into dummy'
from dual
where exists
(select 1
 from users u, usermap um, applications ap

 where um.user_id=u.user_id
 and    um.app_id = ap.app_id
 and    ap.app_name = p_app

 and u.username = p_user);
flag := 1;
exception
when no_data_found then flag := 0;
when others then raise;
end;
/

Hth Received on Thu Apr 03 2008 - 12:35:34 CDT