Logon Trigger DBMS_RANDOM

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Sat, 01 Jan 2000 04:36:02 +0200
Message-ID: <386D6812.765073F@0800-einwahl.de>

I try to initialize DBMS_RANDOM package of Oracle 8.1.5 NT 4 SP 5 from within a logon trigger. The trigger

create or replace trigger tals
after logon
on schema
declare

        n number:= mod (to_number (to_char (sysdate, 'YYYYMMDDHH24MISS'),

'9999999999999999') - 2147483648, 2147483648);
	b binary_integer:= n;
begin
	dbms_random.initialize (b);
	debug.write_debug ('tals fired with n = ' || n);

end tals;
/
show errors

works for the debug.write_debug which merely puts a text line with a timestamp to d:\temp\write_debug.lst.

However, if I invoke in the session that fired the trigger anything with dbms_random.random, I get the error

ERROR at line 1:

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_RANDOM", line 13
ORA-06512: at "SYS.DBMS_RANDOM", line 33

If I run the body of the trigger (declare... end) with cut & paste explicitly before that, the error does not appear.

I read in the docs that logon triggers are put in autonomous transaction mode. Does this affect the initialisation of dbms_random package?

Any hints appreciated.

Martin Received on Fri Dec 31 1999 - 20:36:02 CST