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

From: BD <bobby_dread_at_hotmail.com>
Date: 25 May 2006 10:14:56 -0700
Message-ID: <1148577296.267122.152400@i39g2000cwa.googlegroups.com>

>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;
/ Received on Thu May 25 2006 - 12:14:56 CDT