Re: trying to figure out a truncate trigger issue
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- Date: Wed, 13 Jan 2010 10:20:42 -0500
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.
Other related posts:
