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

On 25 May 2006 10:14:56 -0700, "BD" <bobby_dread_at_hotmail.com> 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;
>/

1 Trigger code is parsed for every call. Replace this by a stored procedure, and you loose all of the parsing 2 Many of your selects from v$session can and should be replaced by calling the sys_context function
3 audsid is always 0 for user SYS and any user with SYSDBA. Your code is going to fail, as all SYS users have the same audsid, 0 4 If you are going to reject users, the execute immediate should be the last statement before logging in.
5 Currently you don't allow sys to connect. That is going to be a problem.

--
Sybrand Bakker, Senior Oracle DBA