Thank you very much, Jonathan! I got it (not a bug) so perhaps
RAISE_APPLICATION_ERROR is a good solution.
Best Regards
Quanwen Zhao
FreeLists Mailing List Manager <ecartis@xxxxxxxxxxxxx> 于2021年10月18日周一
上午9:05写道：
oracle-l Digest Sun, 17 Oct 2021 Volume: 18 Issue: 272
In This Issue:
Fwd: Don't sure whether it's a bug for raising an
exception
Re: Don't sure whether it's a bug for raising an exception
m
----------------------------------------------------------------------
From: Quanwen Zhao <quanwenzhao@xxxxxxxxx>
Date: Sun, 17 Oct 2021 09:46:35 +0800
Subject: Fwd: Don't sure whether it's a bug for raising an exception
manually?
---------- Forwarded message ---------
хПСф╗╢ф║║я╝Ъ Quanwen Zhao <quanwenzhao@xxxxxxxxx>
Date: 2021х╣┤10цЬИ16цЧехСихЕн ф╕ЛхНИ6:26
Subject: Don't sure whether it's a bug for raising an exception manually?
To: <oracle-l@xxxxxxxxxxxxx>
Hello my oracle friends ,
I played with a demo on my schema *c##test* on oracle *21.3*.
- create *4* number of table (*t1, t2, t3, t4*), their segment size is
decreased sequentially;
- create a *trigger* to prevent you from creating the copy table with
t1/t2/t3 on the aforementioned schema;
- prompt the warning message by *raising an exception* automatically or
manually;
But handling the exception (using *"RAISE_EXCEPTION_ERROR"* is nice), why
handling the exception manually (using *"exception_name EXCEPTION; PRAGMA
EXCEPTION_INIT(exception_name,-24381); RAISE exception_name;"*) has never
been captured? Is it a bug?
The following is my entire code:
-- on schema *c##test* on *21.3*:
create table t1 as select * from all_objects;
lower(v_big_table.segment_name)
begin
for i in 1 .. 40 loop
insert into t1 value select * from all_objects;
end loop;
commit;
end;
/
create table t2 as select * from all_objects;
begin
for i in 1 .. 30 loop
insert into t2 value select * from all_objects;
end loop;
commit;
end;
/
create table t3 as select * from all_objects;
begin
for i in 1 .. 20 loop
insert into t3 value select * from all_objects;
end loop;
commit;
end;
/
create table t4 as select * from all_objects;
begin
for i in 1 .. 10 loop
insert into t4 value select * from all_objects;
end loop;
commit;
end;
/
col segment_name for a15
select segment_name
, sum(bytes)/power(2, 20) size_mb
from user_segments
where segment_type TABLE'
group by segment_name
order by 2
desc
;
SEGMENT_NAME SIZE_MB
--------------- ----------
T1 400
T2 304
T3 208
T4 112
create or replace trigger no_ctas_big_table
before create on c##test.schema
declare
l_ddl long;
l_sqltxt ora_name_list_t;
n number;
t_name varchar2(35);
cursor big_table_cur is
select *
from
(
select segment_name
, sum(bytes)/power(2, 20) size_mb
from user_segments
where segment_type TABLE'
group by segment_name
order by 2
desc
)
where rownum < > ;
begin
n : ra_sql_txt( l_sqltxt );
for i in 1..n
loop
l_ddl : _ddl || l_sqltxt(i);
end loop;
l_ddl : ower(trim(l_ddl));
for v_big_table in big_table_cur loop
if l_ddl like
'create%table%as%select%from%'||lower(v_big_table.segment_name)||'%'
and l_ddl not like '%nologging%'
-- and regexp_substr(l_ddl, '[^ ]+', 1, 8) >
then||
t_name : _big_table.segment_name;
-- dbms_output.put_line('Do not allow to CTAS big table ' || t_name ||
' without keyword nologging.');
raise_application_error(-20001, 'Do not allow to CTAS big table '
t_name || ' without keyword nologging.');
else
continue;
end if;
end loop;
end;
/
*15:34:11 C##TEST@ORACDB> create table t as select * from t1;*
*create table t as select * from t1*
* **
*ERROR at line 1:*
*ORA-04088: error during execution of trigger 'C##TEST.NO_CTAS_BIG_TABLE'*
*ORA-00604: error occurred at recursive SQL level 1*
*ORA-20001: Do not allow to CTAS big table T1 without keyword nologging.*
*ORA-06512: at line 37*
*ORA-06512: at line 37*
*15:34:21 C##TEST@ORACDB> create table t as select * from t2;*
*create table t as select * from t2*
* **
*ERROR at line 1:*
*ORA-04088: error during execution of trigger 'C##TEST.NO_CTAS_BIG_TABLE'*
*ORA-00604: error occurred at recursive SQL level 1*
*ORA-20001: Do not allow to CTAS big table T2 without keyword nologging.*
*ORA-06512: at line 37*
*ORA-06512: at line 37*
*15:34:25 C##TEST@ORACDB> create table t as select * from t3;*
*create table t as select * from t3*
* **
*ERROR at line 1:*
*ORA-04088: error during execution of trigger 'C##TEST.NO_CTAS_BIG_TABLE'*
*ORA-00604: error occurred at recursive SQL level 1*
*ORA-20001: Do not allow to CTAS big table T3 without keyword nologging.*
*ORA-06512: at line 37*
*ORA-06512: at line 37*
create or replace trigger no_ctas_big_table
before create on c##test.schemalower(v_big_table.segment_name)
declare
l_ddl long;
l_sqltxt ora_name_list_t;
n number;
t_name varchar2(35);
*ctas_err EXCEPTION;*
* PRAGMA EXCEPTION_INIT(ctas_err, -24381);*
cursor big_table_cur is
select *
from
(
select segment_name
, sum(bytes)/power(2, 20) size_mb
from user_segments
where segment_type TABLE'
group by segment_name
order by 2
desc
)
where rownum < > ;
begin
n : ra_sql_txt( l_sqltxt );
for i in 1..n
loop
l_ddl : _ddl || l_sqltxt(i);
end loop;
l_ddl : ower(trim(l_ddl));
for v_big_table in big_table_cur loop
if l_ddl like
'create%table%as%select%from%'||lower(v_big_table.segment_name)||'%'
and l_ddl not like '%nologging%'
-- and regexp_substr(l_ddl, '[^ ]+', 1, 8) >
then||
t_name : _big_table.segment_name;
-- dbms_output.put_line('Do not allow to CTAS big table ' || t_name ||
' without keyword nologging.');
-- raise_application_error(-20001, 'Do not allow to CTAS big table '
t_name || ' without keyword nologging.');'
*raise ctas_err;*
else
continue;
end if;
end loop;
*exception*
* when ctas_err then*
* dbms_output.put_line('Do not allow to CTAS big table ' || t_name ||
without keyword nologging.');*
end;
/
*15:37:28 C##TEST@ORACDB> create table t as select * from t1;*
*Table created.*
*15:37:34 C##TEST@ORACDB> create table tt as select * from t2;*
*Table created.*
*15:38:14 C##TEST@ORACDB> create table ttt as select * from t3;*
*Table created.*
Could you help me to solve my confusion? Thank you beforehand!
Best Regards
Quanwen Zhao
------------------------------
From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
Date: Sun, 17 Oct 2021 07:57:45 +0100
Subject: Re: Don't sure whether it's a bug for raising an exception
manually?
In your exception clause you've handled the error (ctas_error) so the error
exists no more and the trigger code can complete successfully.
If you want an error to be passed on upwards (i.e. for the trigger to fail)
you have to raise another exception; typically you do this with the key
word "raise;" though sometimes people will (as you have done in the first
version of the code) "raise_application_error(......)" with some text that
you think is more informative that the internal Oracle error message.
Regards
Jonathan Lewis
On Sun, 17 Oct 2021 at 02:47, Quanwen Zhao <quanwenzhao@xxxxxxxxx> wrote:
---------- Forwarded message ---------
хПСф╗╢ф║║я╝Ъ Quanwen Zhao <quanwenzhao@xxxxxxxxx>
Date: 2021х╣┤10цЬИ16цЧехСихЕн ф╕ЛхНИ6:26
Subject: Don't sure whether it's a bug for raising an exception manually?
To: <oracle-l@xxxxxxxxxxxxx>
*exception*
* when ctas_err then*
* dbms_output.put_line('Do not allow to CTAS big table ' || t_name ||
' without keyword nologging.');*
end;
/
------------------------------
End of oracle-l Digest V18 #272
*******************************
