if x not in(select y from table) in oracle

Question

I'm fairly new when it comes to pl/sql and oracle, and I'm having some trouble. I'm trying to make a logon trigger that will only allow hosts in something like an "approved_users" table to access a schema. Currently I'm using:

 Create or Replace Trigger test_trigger
 AFTER logon on TEST.SCHEMA
 declare
 vhost VARCHAR2(25);
 begin
 select sys_context('userenv', 'host') into vhost from dual;
 if vhost not in(select hostname from approved_users) THEN
 raise_application_error(-20001, 'Access Denied');
 end if;
 end;

But I'm getting a message saying, "compiled but with errors" and I'm pretty sure that it's not liking the "if not in(select...." portion of the code. I'm hoping somebody here can give me some help on how to get this working/other ways to go about making this logon trigger.

Thanks.

Answer 1

Instead of:

if vhost not in(select hostname from approved_users) THEN
   raise_application_error(-20001, 'Access Denied');
end if;

You can do this:

select count(*)
into   v_count -- v_count integer;
from   approved_users
where  hostname  = vhost
and    rownum = 1; -- *see below

if v_count = 0 then
   raise_application_error(-20001, 'Access Denied');
end if;

NOTE: The condition rownum=1 is to make the query efficient if it can return more than 1 row, because as soon as you find 1 row you know the answer. You can remove this line if it isn't needed.