Re: QUESTION: raise_application_error results in ORA-00604...?

BD wrote:
> >IIRC a simple exception section with a single dbms_output.put_line message is sufficient.
>
> Hmmm... doesn't seem to be working.
>
> If I put in a raise_application_error (-20001) as the exception action,
> it rejects the logon, but returns the ORA-00604 as well. If I use a
> dbms_output.put_line, it does nothing. I know the exception is being
> raised because I also tested it with an insert into an audit table as
> the exception action, and the insert was performed.
>
> So the dbms_output.put_line on its own doesn't seem to do it.
>
> No big deal; it works - it'd just be nice if I didn't see that 00604.
>
> Here's my code, fwiw.
>
> create or replace trigger browse_disconnect
> after logon on database
> declare
> v_username varchar2 (30);
> v_osuser varchar2 (30);
> v_servername varchar2 (64);
> v_program varchar2 (48);
> v_terminal varchar2 (30);
> invalid_logon EXCEPTION;
> begin
> execute immediate 'alter session set nls_date_format=''YYYY-MM-DD
> hh:mi:ss'' ';
> select upper (host_name) into v_servername from ctbdba.v_host_name;
> -- this is a view which selects host_name from v$instance
> select user into v_username from dual;
> select upper (program) into v_program from v$session where audsid =
> userenv('sessionid');
> select upper (osuser) into v_osuser from v$session where audsid =
> userenv('sessionid');
> select upper (terminal) into v_terminal from v$session where audsid
> = userenv('sessionid');
> if upper (v_servername) = 'PROD_SERVER' and
> upper (v_username) = 'BROWSE_ID' and
> upper (v_osuser) in ('VALID_ID_1, 'VALID_ID_2') then
> insert into ctbdba.logon_audit values (v_osuser, v_username,
> v_terminal, v_program, sysdate);
> commit;
> raise invalid_logon;
> end if;
> EXCEPTION
> WHEN invalid_logon then
> dbms_output.put_line ('GO AWAY'); -- doesn't seem to have much
> effect, even though the exception is being raised.
> end browse_disconnect;
> /

The error you are seeing is caused not by your RAISE statement, but rather by 'commit' which is illegal in the trigger. So you are just lucky that it 'works', sort of.

If you get rid of 'commit', your trigger still won't work because you intercept the exception you've just raised, and the login won't be denied. So you need to get rid both of the commit and the EXCEPTION clause. Received on Thu May 25 2006 - 15:10:30 CDT