Re: Pass a variable into procedure to use as column in select statement
- From: OutCast <Joe.r.rhea@xxxxxxxxxxxx>
- Date: Thu, 3 Apr 2008 12:11:43 -0700 (PDT)
On Apr 3, 12:57 pm, OutCast <Joe.r.r...@xxxxxxxxxxxx> 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.
.
- Follow-Ups:
- References:
- Prev by Date: Re: External Tables (support DML)
- Next by Date: Re: Capitalize first letter of FIRST word
- Previous by thread: Re: Pass a variable into procedure to use as column in select statement
- Next by thread: Re: Pass a variable into procedure to use as column in select statement
- Index(es):
Relevant Pages
|
