CODEWITHSUNDEEP
sqloracle-databasefunctionplsql
Yaser Sleiman
Yaser Sleiman

Reputation: 95

PL/SQL Function Logic

So recently i've started experimenting with databases and i'm still new and although i'm getting several examples to do with functions..I can't seem to figure out what's wrong with my logic.

So I have a table called "tutorial" and i'm trying to just match a row from within the table to the logged in user. I'm sure i've set the logged in user to v_user right..so it's just the logic in the following code I think.

if v_emp_no != 0 and (select tutor_emp_no into v_tutor_emp_no from tutorial where
lower(tutor_emp_no) = v_user) then

 -- Employee is a Tutor
 is_tutor := 1;
end if;

Of course this is not the full function :)

Any help or advice would be greatly appreciated! :)

** EDIT **

This is the policy that works in the example given to me under a created user 'sec_admin' using oe owned tables whereas doesn't work in my given sys role user named 'dummy'. 

begin
dbms_rls.add_policy('oe',
                    'enrol',
                    'accesscontrol_enrol',
                    'sys',
                    'f_policy_enrol',
                    policy_type => dbms_rls.context_sensitive);
end;
/

Should i change the 'oe' to 'dummy' or 'sys'? The table I want this policy to work for is called enrol and it's a table that i've created through my dummy user. Thanks again!

Upvotes: 0

Views: 202

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

We cannot specify a SQL inside IF statement, so let's first query and then check the result in the IF

select MAX(tutor_emp_no) into v_tutor_emp_no from tutorial where
lower(tutor_emp_no) = v_user;

if v_emp_no != 0 and v_tutor_emp_no is NOT NULL then

 -- Employee is a Tutor
 is_tutor := 1;
end if;

The MAX() is just to return NULL if no records found.!

Upvotes: 1

Related Questions