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