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