Logic in Where Clause

[szeiss]

I'm not sure how to do this or if it's possible.

I have a function called reportaccess in Oracle 10g. I'm passing a parameter called p_permission which will either be 'true' or 'false'.

In the where clause I would like to do something like this:

Where
blah = whatever
and whatever = something
If p_permission = 'true' then
and employee_id = p_empid
End if;
and wow = great

I'm a newbie so please if this can be done, please provide code.

Thanks,
Sherry

 

[Thargy]

You can't do that because oracle does not store boolean values in tables. It only uses booleans in PL/SQL.

A typical work around is to store zero and 1 as a representation of true and false, and then compare values with them instead.

Regards

T

 

[Turkbear]

Hi,
If I understand the issue, you are passing p_permission as a string to a function in order to determine if someone is authorized to view data in a table or tables.

Where does p_empid come from?

Look into the CASE statement in Oracle for some ideas on implementing conditional criteria in a where clause.see example here:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref527

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[szeiss]

Yes, p_empid is also being passed into the function.

Sherry

 

[Turkbear]

Hi,
OK, 2 approaches..Validate the p_empid BEFORE sending it to the database thereby eliminating the need for the True or False parameter, or use the CASE logic shown in that site's examples.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[roadling]

Forgive me if I don't fully understand the problem you are trying to solve here but, why wouldn't you create the appropriate users and roles in Oracle to control access to the database objects?

You can, depending on the version of Oracle 10g you are running, create as fine grained access as you need.

 

[ChrisHunt]

Something along these lines?

Where
        blah = whatever
    and whatever = something
    and (p_permission = 'false' or employee_id = p_empid)
    and wow = great

That might not be the most efficient approach though.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Dagon]

Another approach would simply be to use different SQL depending on the condition e.g.

if p_permission = false then
   ...
    Where blah = whatever
    and whatever = something
    and wow = great
else
    ...
    where blah = whatever
    and whatever = something
    and employee_id = p_empid
    and wow = great
end if;

It depends on the complexity of the code and whether you are willing to tolerate duplicating parts of it or not.

If you don't like that, a further option would be dynamic SQL e.g.

 v_sql := '  ...
    where blah = whatever
    and whatever = something
    and wow = great';

if if p_permission = true then
  v_sql := v_sql || ' and employee_id = '||p_empid;
end if;

open v_curs for v_sql;
...