Is it possible to truncate the table before shutdown on trigger

[A Sundar]

Hi,
 

  How to truncate the table before shutdown on trigger. The below sample code does work.


CREATE OR REPLACE TRIGGER TETS_PURGE BEFORE SHUTDOWN ON DATABASE
begin
execute immediate 'TRUNCATE TABLE TEST_TABLE';
END;
/

Regards,

Sundar

[ddf]

It is not possible to truncate a table with a shutdown trigger, I imagine because DDL isn't allowed from a system trigger, even using execute immediate.  You can, though, delete data from the table:

SQL> create table test_table(
  2          yoopa   number,
  3          hungwe  varchar2(40),
  4          uytre   date,
  5          klopu   varchar2(100));

Table created.

SQL>
SQL> begin
  2          for i in 1..100000 loop
  3                  insert into test_table
  4                  values(i, 'Beezo'||i, sysdate+i, 'Hoobnertz valyrand qwertins'||i);
  5          end loop;
  6
  7          commit;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from bing.test_table;

  COUNT(*)
----------
    100000

SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> CREATE OR REPLACE TRIGGER TETS_PURGE BEFORE SHUTDOWN ON DATABASE
  2  declare
  3          pragma autonomous_transaction;
  4  begin
  5          -- execute immediate 'TRUNCATE TABLE BING.TEST_TABLE';
  6          delete from bing.test_table;
  7          commit;
  8  END;
  9  /

Trigger created.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1670221824 bytes
Fixed Size                  2281816 bytes
Variable Size             956305064 bytes
Database Buffers          704643072 bytes
Redo Buffers                6991872 bytes
Database mounted.
Database opened.
SQL>
SQL> connect bing/bong

Connected.

SQL>
SQL> select count(*) from bing.test_table;

  COUNT(*)
----------
         0

SQL>

Even an after startup trigger won't process the 'execute immediate' so the table remains unchanged.

You'll have to settle for a delete.

David Fitzjarrell