Re: oracle-l Digest V18 #272

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_at_freelists.org> 于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_at_gmail.com>
> 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_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>
>
> 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;
> >
> > 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) >
> lower(v_big_table.segment_name)
> > 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_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.*
> *ORA-06512: at line 37*
> *ORA-06512: at line 37*
>
>
> *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 < > ;
> > 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) >
> lower(v_big_table.segment_name)
> > 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_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
>
>
> ------------------------------
>
> From: Jonathan Lewis <jlewisoracle_at_gmail.com>
> 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_at_gmail.com> wrote:
>
> >
> >
> > ---------- 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>
> >
> > *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
> *******************************
>
>

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