Startup Trigger Can't Do DDL ...

Somebody out there please try this ...

create sequence sys.test_seq start with 1 nocache;

create trigger drop_seq
after startup on database
declare

   pragma autonomous_transaction;
begin

   execute immediate 'drop sequence sys.test_seq'; end;
/

connect / as sysdba
shutdown immediate
startup

<the sequence is still there>

is this a bug??

I've also had random problems with execute immediate when it is surrounded by other cursors. Anyone else seen this?

What I am trying to do is have the database startup drop and recreate sequences to eliminate gaps caused by shutdown aborts or system crashes -- ie when the sequences are cached this happens.

Thanks in advance for all replies ... Received on Thu Apr 29 2004 - 16:59:19 CDT