Re: Pass a variable into procedure to use as column in select statement
Date: Thu, 3 Apr 2008 12:11:43 -0700 (PDT)
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
> 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)
> v_Access varchar(1);
> 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
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