Ian and everyone else, thanks, right after I sent the request i'd found the ora_sql_txt thing. joe _______________________________________ Joe Testa, Oracle Certified Professional Senior Engineering & Administration Lead (Work) 614-677-1668 (Cell) 614-312-6715 Interested in helping out your marriage? Ask me about "Weekend to Remember" Feb 12-Feb 14, 2010, Valentine's Day Weekend in Akron, OH From: Ian Cary <ian.cary@xxxxxxxxxxxxxx> To: TESTAJ3@xxxxxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx Date: 01/13/2010 10:18 AM Subject: Re: trying to figure out a truncate trigger issue This seems to work; drop table x1_test; create table x1_test ( z date) partition by range (z) (partition part_1999 values less than (to_date('01-jan-2000','dd-mon-yyyy')), partition part_2000 values less than (to_date('01-jan-2001','dd-mon-yyyy')) ); create or replace trigger x1_test_trig before truncate on schema declare sql_text ora_name_list_t; sql_cmd clob := null; x number; begin x := ora_sql_txt(sql_text); if x is null then sql_cmd := null; else for i in 1..x loop sql_cmd := sql_cmd || sql_text(i); end loop; end if; if lower(sql_cmd) like 'alter%' then null; else raise_application_error('-20001','No truncate allowed'); end if; end; alter table x1_test truncate partition part_1999; Table truncated. truncate table x1_test; truncate table x1_test * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-20001: No truncate allowed ORA-06512: at line 18 Cheers, Ian |---------+-----------------------------> | | TESTAJ3@nationwide| | | .com | | | Sent by: | | | oracle-l-bounce@fr| | | eelists.org | | | | | | | | | 13/01/2010 14:58 | | | Please respond to | | | TESTAJ3 | | | | |---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------------------------------------------| | | | To: oracle-l@xxxxxxxxxxxxx | | cc: | | Subject: trying to figure out a truncate trigger issue | >--------------------------------------------------------------------------------------------------------------------------------------------------| I need to be able to run a "alter table truncate partition" but NOT a truncate table. I can't seem to figure out how to separate those 2 commands, it appears to a schema level trigger for truncate as they are the same. test code below: thanks, joe ------------------------------------------ drop table x1_test; create table x1_test ( z date) partition by range (z) (partition part_1999 values less than (to_date('01-jan-2000','dd-mon-yyyy')), partition part_2000 values less than (to_date('01-jan-2001','dd-mon-yyyy')) ); create or replace trigger x1_test_trig before truncate on schema begin raise_application_error('-20001','No truncate allowed'); end; / alter table x1_test truncate partition part_1999; _______________________________________ Joe Testa, Oracle Certified Professional Senior Engineering & Administration Lead (Work) 614-677-1668 (Cell) 614-312-6715 Interested in helping out your marriage? Ask me about "Weekend to Remember" Feb 12-Feb 14, 2010, Valentine's Day Weekend in Akron, OH This email was received from the INTERNET and scanned by the Government Secure Intranet anti-virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) In case of problems, please call your organisation?x02019;s IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk ********************************************************************************* Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications ********************************************************************************* Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics ********************************************************************************* The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2009/09/0052.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.