create logon trigger

From: <emdproduction_at_hotmail.com>
Date: Fri, 25 Apr 2008 13:52:34 -0700 (PDT)
Message-ID: <381dd266-90c3-45e7-8b2d-20afe2ecc087@f36g2000hsa.googlegroups.com>

Dear group,

I want to maintain a table, so that if people in this table, they can logon using sqlplus, if they are not, they can only logon through our application server.

So

this trigger works fine

---

CREATE OR REPLACE TRIGGER rds_logon_trigger   2 AFTER LOGON ON DATABASE
  3 BEGIN
  4 IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.1','192.168.2.2','192.168.2.3') THEN   5 RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
  6 END IF;
  7* end;

---

But if i want to use a query, I got an error

---

CREATE OR REPLACE TRIGGER rds_logon_trigger AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in (select '1' from dual) THEN
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;
end;
2/47 PLS-00405: subquery not allowed in this context

Is there any way I can achieve what i wanted?

Thanks for your help Received on Fri Apr 25 2008 - 15:52:34 CDT