Fwd: Don't sure whether it's a bug for raising an exception manually?

• Forwarded message --------- 发件人： Quanwen Zhao <quanwenzhao_at_gmail.com> Date: 2021年10月16日周六 下午6:26 Subject: Don't sure whether it's a bug for raising an exception manually? To: <oracle-l_at_freelists.org>

• 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;
>
> 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 <=3
> ;
> begin
> n := ora_sql_txt( l_sqltxt );
>
> for i in 1..n
> loop
> l_ddl := l_ddl || l_sqltxt(i);
> end loop;
>
> l_ddl := lower(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) =
> lower(v_big_table.segment_name)
> then
> t_name := v_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_at_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_at_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.*

*15:34:25 C##TEST_at_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.schema
> 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 <=3
> ;
> begin
> n := ora_sql_txt( l_sqltxt );
>
> for i in 1..n
> loop
> l_ddl := l_ddl || l_sqltxt(i);
> end loop;
>
> l_ddl := lower(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) =
> lower(v_big_table.segment_name)
> then
> t_name := v_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_at_ORACDB> create table t as select * from t1;*

*Table created.*

*15:37:34 C##TEST_at_ORACDB> create table tt as select * from t2;*

*Table created.*

*15:38:14 C##TEST_at_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

--
http://www.freelists.org/webpage/oracle-l