Raising Error Messages from an AFTER LOGON Trigger

[MikeJones]

Hi everyone,

I'm trying to write a trigger that will, on login perform some SQL and based on this possibly raise an exception. I.e. when the user logs in they may get:

ORA 20001, My error Text here

The problem is I can't get SQL Plus to display the error message on Login, It is however hitting the exception. My code is below

The package call in the exception handler is to a autonamous transaction that logs the error in a table and then does a raise application error, this is a standard package so I know it works (I've also tried it by just calling it from within a begin end;

The call to the package is also working as the error is getting logged in the audit table.

CREATE OR REPLACE TRIGGER mike_test
AFTER LOGON
ON brftest.schema
DECLARE
bad_user EXCEPTION;
BEGIN
if upper(user) = 'BRFTEST' then
raise bad_user;
end if;
insert into mike values ('Logged in at ' || to_char(sysdate,'HH24:MI:SS'));
EXCEPTION
WHEN bad_user THEN
omx_oam_cmn_01.gv_ora_err := 'Get Lost';
omx_oam_cmn_01.report_event(0,
'SECURITY',
'Login',
user);
RAISE;
RAISE;
WHEN OTHERS THEN
omx_oam_cmn_01.gv_ora_err := sqlerrm;
omx_oam_cmn_01.report_event(0,
'Security',
'LOGIN',
user);
END;
/

BTW, This is on an 8.1.6 Database...

Thanks in advance,

Mike.

 

[listener22]

Don't you have to throw in a pragma exception_init (exception_name,
error_code_literal)
after the declaration of the user-defined exception?

Not sure, but that's the way I've seen it done. Jim

oracle, vb

 

[MikeJones]

I hope not!! The problem ids I don'y know what error_code_literal will be used as part of the trigger, the Package procedure report event looks this up..

For example the following is a straight cut and paste from SQLPLus....


Wrote file afiedt.buf

1 declare
2 bad_user exception;
3 begin
4 raise bad_user;
5 exception
6 WHEN bad_user THEN
7 omx_oam_cmn_01.gv_ora_err := 'Piss Off Jonesy';
8 omx_oam_cmn_01.report_event(0,
9 'SECURITY',
10 'Login',
11 user);
12 when others then null;
13* end;
16:48:01 SQL> /
declare
*
ERROR at line 1:
ORA-20999: Piss Off Jonesy
ORA-06512: at "BRFTEST.OMX_OAM_CMN_01", line 229
ORA-06512: at line 8

 

[listener22]

In that example, your literal would be -20999. Jim

oracle, vb

 

[MikeJones]

Jim,

In my second example I haven't included a PRAGMA clause and it's all worked fine. Why should I need one in the trigger?>

Mike.

 

[listener22]

I'm not sure. But where is it that you want to display the error from the
trigger -- on the (bad) user's screen? It seems that if the error is being
logged without the pragma statement, that the problem is getting the
message to a particular screen. Jim

oracle, vb

 

[MikeJones]

Fair point, I'd like the error message displayed on the users screen. In actuality the user will not log in via SQL Plus but via a Delphi front end. The exception should returned to it. I'm not a Delphi person though, so I thought if I can get the exception returned to SQL Plus I'd be able to get the Delphi front end to see it too.

Mike.

P.S. If you have any further ideas here's how the code looks now...


set arraysize 1
--
CREATE OR REPLACE TRIGGER mike_test
AFTER LOGON
ON brftest.schema
DECLARE
bad_user EXCEPTION;
--
PRAGMA EXCEPTION_INIT(bad_user, -20999);
--
BEGIN
if upper(user) = 'BRFTEST' then
raise bad_user;
end if;
insert into mike values ('Logged in at ' || to_char(sysdate,'HH24:MI:SS'));
EXCEPTION
WHEN bad_user THEN
omx_oam_cmn_01.gv_ora_err := 'Piss Off Jonesy';
omx_oam_cmn_01.report_event(0,
'SECURITY',
'Login',
user);
RAISE;
RAISE;
WHEN OTHERS THEN
omx_oam_cmn_01.gv_ora_err := sqlerrm;
omx_oam_cmn_01.report_event(0,
'Security',
'LOGIN',
user);
END;
/
show errors

 

[MikeJones]

Found the Problem, The trigger needs to be installed from the sys user.