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

You need to use Dynamic SQL to achieve this. Construct the select statement at run time and execute that using EXECUTE IMMEDIATE (I you are on latest version of Oracle)

for example

I have got a variable v_col_name which stores the name of the column and i have to execute this query. What I will do is

v_string := 'select '||v_col_name||' from table_name'; execute immediate v_string;

Hope that helps
Amardeep Sidhu

On Apr 3, 9: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
Received on Thu Apr 03 2008 - 12:33:57 CDT