| blob | 0a232dbc2c4136653041a4683ae3c533340927a4 |
1 /*$mw$*/
2 CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname IN VARCHAR2,
3 p_oldprefix IN VARCHAR2,
4 p_newprefix IN VARCHAR2,
5 p_temporary IN BOOLEAN) IS
6 e_table_not_exist EXCEPTION;
7 PRAGMA EXCEPTION_INIT(e_table_not_exist, -00942);
8 l_temp_ei_sql VARCHAR2(2000);
9 l_temporary BOOLEAN := p_temporary;
10 BEGIN
11 BEGIN
12 EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
13 ' CASCADE CONSTRAINTS';
14 EXCEPTION
15 WHEN e_table_not_exist THEN
16 NULL;
17 END;
18 IF (p_tabname = 'SEARCHINDEX') THEN
19 l_temporary := FALSE;
20 END IF;
21 IF (l_temporary) THEN
22 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
23 p_tabname || ' AS SELECT * FROM ' || p_oldprefix ||
24 p_tabname || ' WHERE ROWNUM = 0';
25 ELSE
26 EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname ||
27 ' AS SELECT * FROM ' || p_oldprefix || p_tabname ||
28 ' WHERE ROWNUM = 0';
29 END IF;
30 FOR rc IN (SELECT column_name, data_default
31 FROM user_tab_columns
32 WHERE table_name = p_oldprefix || p_tabname
33 AND data_default IS NOT NULL) LOOP
34 EXECUTE IMMEDIATE 'ALTER TABLE ' || p_newprefix || p_tabname ||
35 ' MODIFY ' || rc.column_name || ' DEFAULT ' ||
36 SUBSTR(rc.data_default, 1, 2000);
37 END LOOP;
38 FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('CONSTRAINT',
39 constraint_name),
40 32767,
41 1),
42 USER || '"."' || p_oldprefix,
43 USER || '"."' || p_newprefix),
44 '"' || constraint_name || '"',
45 '"' || p_newprefix || constraint_name || '"') DDLVC2,
46 constraint_name
47 FROM user_constraints uc
48 WHERE table_name = p_oldprefix || p_tabname
49 AND constraint_type = 'P') LOOP
50 l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1);
51 l_temp_ei_sql := SUBSTR(l_temp_ei_sql,
52 1,
53 INSTR(l_temp_ei_sql,
54 ')',
55 INSTR(l_temp_ei_sql, 'PRIMARY KEY') + 1) + 1);
56 IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
57 EXECUTE IMMEDIATE l_temp_ei_sql;
58 END IF;
59 END LOOP;
60 IF (NOT l_temporary) THEN
61 FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT',
62 constraint_name),
63 32767,
64 1),
65 USER || '"."' || p_oldprefix,
66 USER || '"."' || p_newprefix) DDLVC2,
67 constraint_name
68 FROM user_constraints uc
69 WHERE table_name = p_oldprefix || p_tabname
70 AND constraint_type = 'R') LOOP
71 IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
72 EXECUTE IMMEDIATE l_temp_ei_sql;
73 END IF;
74 END LOOP;
75 END IF;
76 FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
77 index_name),
78 32767,
79 1),
80 USER || '"."' || p_oldprefix,
81 USER || '"."' || p_newprefix),
82 '"' || index_name || '"',
83 '"' || p_newprefix || index_name || '"') DDLVC2,
84 index_name,
85 index_type
86 FROM user_indexes ui
87 WHERE table_name = p_oldprefix || p_tabname
88 AND index_type NOT IN ('LOB', 'DOMAIN')
89 AND NOT EXISTS
90 (SELECT NULL
91 FROM user_constraints
92 WHERE table_name = ui.table_name
93 AND constraint_name = ui.index_name)) LOOP
94 l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'PCTFREE') - 1);
95 l_temp_ei_sql := SUBSTR(l_temp_ei_sql,
96 1,
97 INSTR(l_temp_ei_sql,
98 ')',
99 INSTR(l_temp_ei_sql,
100 '"' || USER || '"."' || p_newprefix || '"') + 1) + 1);
101 IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
102 EXECUTE IMMEDIATE l_temp_ei_sql;
103 END IF;
104 END LOOP;
105 FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
106 index_name),
107 32767,
108 1),
109 USER || '"."' || p_oldprefix,
110 USER || '"."' || p_newprefix),
111 '"' || index_name || '"',
112 '"' || p_newprefix || index_name || '"') DDLVC2,
113 index_name,
114 index_type
115 FROM user_indexes ui
116 WHERE table_name = p_oldprefix || p_tabname
117 AND index_type = 'DOMAIN'
118 AND NOT EXISTS
119 (SELECT NULL
120 FROM user_constraints
121 WHERE table_name = ui.table_name
122 AND constraint_name = ui.index_name)) LOOP
123 l_temp_ei_sql := rc.ddlvc2;
124 IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
125 EXECUTE IMMEDIATE l_temp_ei_sql;
126 END IF;
127 END LOOP;
128 FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER',
129 trigger_name),
130 32767,
131 1)),
132 USER || '"."' || p_oldprefix,
133 USER || '"."' || p_newprefix),
134 ' ON ' || p_oldprefix || p_tabname,
135 ' ON ' || p_newprefix || p_tabname) DDLVC2,
136 trigger_name
137 FROM user_triggers
138 WHERE table_name = p_oldprefix || p_tabname) LOOP
139 l_temp_ei_sql := SUBSTR(rc.ddlvc2, 1, INSTR(rc.ddlvc2, 'ALTER ') - 1);
140 IF nvl(length(l_temp_ei_sql), 0) > 0 THEN
141 EXECUTE IMMEDIATE l_temp_ei_sql;
142 END IF;
143 END LOOP;
144 END;
145 /*$mw$*/