On Monday, January 26, 2015 at 10:33:14 PM UTC-7, sundar wrote:
>
> 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
>
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
--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
---
You received this message because you are subscribed to the Google Groups
"Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
