この記事は約7分0秒で読むことができます。

plsql debug 警告メッセージ制御

参考文献

PLSQL_WARNINGS  
 

この2つ知ってればいいんじゃないか

コード表示

--いろいろでる
ALTER SESSION SET plsql_warnings='ENABLE:ALL';

--いろいろでなくなる
ALTER SESSION SET plsql_warnings='DISABLE:ALL';

こんなかんじで

コード表示


ALTER SESSION SET plsql_warnings='ENABLE:ALL';

CREATE OR REPLACE PROCEDURE all_drop_obj AS CURSOR csr IS WITH sub AS ( SELECT object_type  ,object_name FROM user_objects s1 WHERE EXISTS ( SELECT 1 FROM user_objects s2 WHERE s1.object_type = s2.object_type ) AND NOT EXISTS ( SELECT 1 FROM user_objects s2 WHERE s2.object_type = 'PROCEDURE' AND s2.object_name = 'ALL_DROP_OBJ' AND s1.object_type = s2.object_type AND s1.object_name = s2.object_name ) ) SELECT 'DROP ' || s1.object_type || ' ' || s1.object_name || CASE WHEN s1.object_type = 'TABLE' THEN ' CASCADE CONSTRAINTS PURGE' WHEN s1.object_type IN ( 'SYNONYM'  ,'TYPE' ) THEN ' FORCE' ELSE '' END AS build_sql FROM sub s1; build_sql CLOB; BEGIN build_sql := to_clob(' '); OPEN csr; LOOP BEGIN FETCH csr INTO build_sql; EXIT WHEN csr%notfound; dbms_output.put_line(build_sql); EXECUTE IMMEDIATE build_sql; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('[ '|| SQLCODE||']'||sqlerrm); END; END LOOP; CLOSE csr; END;
/

Procedure ALL_DROP_OBJがコンパイルされました

LINE/COL  ERROR
--------- -------------------------------------------------------------
1/1       PLW-05018: ユニットALL_DROP_OBJはオプションのAUTHID句を省略しました。デフォルト値のDEFINERが使用されました
57/19     PLW-06009: プロシージャ"ALL_DROP_OBJ" OTHERSハンドラは、RAISEまたはRAISE_APPLICATION_ERRORでは終了しません

ALTER SESSION SET plsql_warnings='DISABLE:ALL';

CREATE OR REPLACE PROCEDURE all_drop_obj AS CURSOR csr IS WITH sub AS ( SELECT object_type  ,object_name FROM user_objects s1 WHERE EXISTS ( SELECT 1 FROM user_objects s2 WHERE s1.object_type = s2.object_type ) AND NOT EXISTS ( SELECT 1 FROM user_objects s2 WHERE s2.object_type = 'PROCEDURE' AND s2.object_name = 'ALL_DROP_OBJ' AND s1.object_type = s2.object_type AND s1.object_name = s2.object_name ) ) SELECT 'DROP ' || s1.object_type || ' ' || s1.object_name || CASE WHEN s1.object_type = 'TABLE' THEN ' CASCADE CONSTRAINTS PURGE' WHEN s1.object_type IN ( 'SYNONYM'  ,'TYPE' ) THEN ' FORCE' ELSE '' END AS build_sql FROM sub s1; build_sql CLOB; BEGIN build_sql := to_clob(' '); OPEN csr; LOOP BEGIN FETCH csr INTO build_sql; EXIT WHEN csr%notfound; dbms_output.put_line(build_sql); EXECUTE IMMEDIATE build_sql; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('[ '|| SQLCODE||']'||sqlerrm); END; END LOOP; CLOSE csr; END;
/

Procedure ALL_DROP_OBJがコンパイルされました

Leave a Reply

Your email address will not be published. Required fields are marked *