Re: trying to figure out a truncate trigger issue
Date: Wed, 13 Jan 2010 10:20:42 -0500
Message-ID: <OF38DAA661.8B43D62A-ON852576AA.00543B92-852576AA.00544AD6_at_lnotes-gw.ent.nwie.net>
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_at_ons.gsi.gov.uk>
To:
TESTAJ3_at_nationwide.com
Cc:
oracle-l_at_freelists.org, oracle-l-bounce_at_freelists.org
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_at_nationwide|
| | .com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 13/01/2010 14:58 |
| | Please respond to |
| | TESTAJ3 |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: oracle-l_at_freelists.org |
| 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 13 2010 - 09:20:42 CST