· 8 years ago · Apr 12, 2018, 01:16 PM
1DECLARE
2 V_TEMP_NUM NUMBER(9) := 0;
3BEGIN
4SELECT COUNT(*) INTO V_TEMP_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TMM_TEMP10_SEQ';
5IF V_TEMP_NUM > 0 THEN
6 DROP SEQUENCE TMM_TEMP10_SEQ;
7END IF;
8CREATE SEQUENCE TMM_TEMP10_SEQ
9 MINVALUE 0
10 START WITH 10
11 INCREMENT BY 10
12 CACHE 20;
13END;
14
15CREATE/ALTER/DROP <object> IF EXISTS...
16
17create or replace procedure admin.re_run_ddl (p_sql in varchar2)
18AUTHID CURRENT_USER
19as
20 l_line varchar2(500) default rpad('-',20,'-');
21 l_cr varchar2(2) default chr(10);
22 l_footer varchar2(500) default l_cr||rpad('*',20,'*');
23 l_ignore_txt varchar2(200) default 'IGNORING --> ';
24 ORA_00955 EXCEPTION;
25 ORA_01430 EXCEPTION;
26 ORA_02260 EXCEPTION;
27 ORA_01408 EXCEPTION;
28 ORA_00942 EXCEPTION;
29 ORA_02275 EXCEPTION;
30 ORA_01418 EXCEPTION;
31 ORA_02443 EXCEPTION;
32 ORA_01442 EXCEPTION;
33 ORA_01434 EXCEPTION;
34 ORA_01543 EXCEPTION;
35 ORA_00904 EXCEPTION;
36 ORA_02261 EXCEPTION;
37 ORA_04043 EXCEPTION;
38 ORA_02289 EXCEPTION;
39 PRAGMA EXCEPTION_INIT(ORA_00955, -00955); --ORA-00955: name is already used by an existing object
40 PRAGMA EXCEPTION_INIT(ORA_01430, -01430); --ORA-01430: column being added already exists in table
41 PRAGMA EXCEPTION_INIT(ORA_02260, -02260); --ORA-02260: table can have only one primary key
42 PRAGMA EXCEPTION_INIT(ORA_01408, -01408); --ORA-01408: such column list already indexed
43 PRAGMA EXCEPTION_INIT(ORA_00942, -00942); --ORA-00942: table or view does not exist
44 PRAGMA EXCEPTION_INIT(ORA_02275, -02275); --ORA-02275: such a referential constraint already exists in the table
45 PRAGMA EXCEPTION_INIT(ORA_01418, -01418); --ORA-01418: specified index does not exist
46 PRAGMA EXCEPTION_INIT(ORA_02443, -02443); --ORA-02443: Cannot drop constraint - nonexistent constraint
47 PRAGMA EXCEPTION_INIT(ORA_01442, -01442); --ORA-01442: column to be modified to NOT NULL is already NOT NULL
48 PRAGMA EXCEPTION_INIT(ORA_01434, -01434); --ORA-01434: private synonym to be dropped does not exist
49 PRAGMA EXCEPTION_INIT(ORA_01543, -01543); --ORA-01543: tablespace '<TBS_NAME>' already exists
50 PRAGMA EXCEPTION_INIT(ORA_00904, -00904); --ORA-00904: "%s: invalid identifier"
51 PRAGMA EXCEPTION_INIT(ORA_02261, -02261); --ORA-02261: "such unique or primary key already exists in the table"
52 PRAGMA EXCEPTION_INIT(ORA_04043, -04043); --ORA-04043: object %s does not exist
53 PRAGMA EXCEPTION_INIT(ORA_02289, -02289); --ORA-02289: sequence does not exist
54 procedure p(
55 p_str in varchar2
56 ,p_maxlength in int default 120
57 )
58 is
59 i int := 1;
60 begin
61 dbms_output.enable( NULL );
62
63 while ( (length(substr(p_str,i,p_maxlength))) = p_maxlength ) loop
64 dbms_output.put_line(substr(p_str,i,p_maxlength));
65 i := i + p_maxlength;
66 end loop;
67
68 dbms_output.put_line(substr(p_str,i,p_maxlength));
69 end p;
70begin
71
72 p( 'EXEC:'||l_cr||l_line||l_cr||p_sql||l_cr||l_line );
73
74 execute immediate p_sql;
75
76 p( 'done.' );
77
78exception
79 when ORA_00955 or ORA_01430 or ORA_02260 or ORA_01408 or ORA_00942
80 or ORA_02275 or ORA_01418 or ORA_02443 or ORA_01442 or ORA_01434
81 or ORA_01543 or ORA_00904 or ORA_02261 or ORA_04043 or ORA_02289
82 then p( l_ignore_txt || SQLERRM || l_footer );
83 when OTHERS then
84 p( SQLERRM );
85 p( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
86 p( l_footer );
87 RAISE;
88end;
89/
90show err
91
92prompt clean-up ...
93begin
94 admin.re_run_ddl('drop sequence BLA_BLA_BLA');
95 admin.re_run_ddl('drop procedure BLA_BLA_BLA');
96 admin.re_run_ddl('drop table BLA_BLA_BLA');
97end;
98/