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';
15 WHEN e_table_not_exist
THEN
18 IF (p_tabname
= 'SEARCHINDEX') THEN
22 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
23 p_tabname ||
' AS SELECT * FROM ' || p_oldprefix ||
24 p_tabname ||
' WHERE ROWNUM = 0';
26 EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname ||
27 ' AS SELECT * FROM ' || p_oldprefix || p_tabname ||
30 FOR rc
IN (SELECT column_name, data_default
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);
38 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('CONSTRAINT',
42 USER ||
'"."' || p_oldprefix
,
43 USER ||
'"."' || p_newprefix
),
44 '"' ||
constraint_name ||
'"',
45 '"' || p_newprefix ||
constraint_name ||
'"') DDLVC2
,
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
,
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
;
60 IF (NOT l_temporary
) THEN
61 FOR rc
IN (SELECT REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('REF_CONSTRAINT',
65 USER ||
'"."' || p_oldprefix
,
66 USER ||
'"."' || p_newprefix
) DDLVC2
,
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
;
76 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('INDEX',
80 USER ||
'"."' || p_oldprefix
,
81 USER ||
'"."' || p_newprefix
),
82 '"' || index_name ||
'"',
83 '"' || p_newprefix || index_name ||
'"') DDLVC2
,
87 WHERE table_name = p_oldprefix || p_tabname
88 AND index_type
NOT IN ('LOB', 'DOMAIN')
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
,
100 '"' ||
USER ||
'"."' || p_newprefix ||
'"') + 1) + 1);
101 IF nvl(length(l_temp_ei_sql
), 0) > 0 THEN
102 EXECUTE IMMEDIATE l_temp_ei_sql
;
105 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('INDEX',
109 USER ||
'"."' || p_oldprefix
,
110 USER ||
'"."' || p_newprefix
),
111 '"' || index_name ||
'"',
112 '"' || p_newprefix || index_name ||
'"') DDLVC2
,
116 WHERE table_name = p_oldprefix || p_tabname
117 AND index_type
= 'DOMAIN'
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
;
128 FOR rc
IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('TRIGGER',
132 USER ||
'"."' || p_oldprefix
,
133 USER ||
'"."' || p_newprefix
),
134 ' ON ' || p_oldprefix || p_tabname
,
135 ' ON ' || p_newprefix || p_tabname
) DDLVC2
,
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
;