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
;
12 EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
13 ' CASCADE CONSTRAINTS PURGE';
15 WHEN e_table_not_exist
THEN
18 IF (p_tabname
= 'SEARCHINDEX') THEN
22 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
24 ' ON COMMIT PRESERVE ROWS AS SELECT * FROM ' ||
25 p_oldprefix || p_tabname ||
' WHERE ROWNUM = 0';
27 EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname ||
28 ' AS SELECT * FROM ' || p_oldprefix || p_tabname ||
31 FOR rc
IN (SELECT column_name, data_default
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);
39 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('CONSTRAINT',
43 USER ||
'"."' || p_oldprefix
,
44 USER ||
'"."' || p_newprefix
),
45 '"' ||
constraint_name ||
'"',
46 '"' || p_newprefix ||
constraint_name ||
'"') DDLVC2
,
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
,
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
;
61 IF (NOT l_temporary
) THEN
62 FOR rc
IN (SELECT REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('REF_CONSTRAINT',
66 USER ||
'"."' || p_oldprefix
,
67 USER ||
'"."' || p_newprefix
) DDLVC2
,
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
;
78 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('INDEX',
82 USER ||
'"."' || p_oldprefix
,
83 USER ||
'"."' || p_newprefix
),
84 '"' || index_name ||
'"',
85 '"' || p_newprefix || index_name ||
'"') DDLVC2
,
89 WHERE table_name = p_oldprefix || p_tabname
90 AND index_type
NOT IN ('LOB', 'DOMAIN')
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
,
102 '"' ||
USER ||
'"."' || p_newprefix ||
'"') + 1) + 1);
103 IF nvl(length(l_temp_ei_sql
), 0) > 0 THEN
104 EXECUTE IMMEDIATE l_temp_ei_sql
;
107 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('INDEX',
111 USER ||
'"."' || p_oldprefix
,
112 USER ||
'"."' || p_newprefix
),
113 '"' || index_name ||
'"',
114 '"' || p_newprefix || index_name ||
'"') DDLVC2
,
118 WHERE table_name = p_oldprefix || p_tabname
119 AND index_type
= 'DOMAIN'
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
;
130 FOR rc
IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('TRIGGER',
134 USER ||
'"."' || p_oldprefix
,
135 USER ||
'"."' || p_newprefix
),
136 ' ON ' || p_oldprefix || p_tabname
,
137 ' ON ' || p_newprefix || p_tabname
) DDLVC2
,
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
;