Re: Pass a variable into procedure to use as column in select statement
Date: Thu, 03 Apr 2008 19:35:34 +0200
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
>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:
>(i_User_ID varchar,i_APP_ID varchar, o_User_ID out varchar, o_Password
> select i_APP_ID into v_Access from User_Main where User_ID =
> If v_Access = 'Y' then
> o_User_ID := 'UserName';
> o_Password := 'Password';
> 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
>Thanks in advance
The design of your user_main table is denormalized and consequently
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)
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;
when no_data_found then flag := 0;
when others then raise;
Hth Received on Thu Apr 03 2008 - 12:35:34 CDT