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

On Apr 3, 12:57 pm, OutCast <Joe.r.r..._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

Thanks, problem solved. I added the two new table and it worked perfectly, plus i'll store the knowledge of the "execute immediate" for when I really need it. It is amazing how doing it right always makes things so much easier. Received on Thu Apr 03 2008 - 14:11:43 CDT