Pass a variable into procedure to use as column in select statement
Date: Thu, 3 Apr 2008 09:57:38 -0700 (PDT)
Message-ID: <df4a3ea1-15fe-460b-92ed-389f68cd6994@x41g2000hsb.googlegroups.com>
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 Received on Thu Apr 03 2008 - 11:57:38 CDT