1 define mw_prefix
='{$wgDBprefix}';
4 CREATE OR REPLACE PROCEDURE duplicate_table(p_tabname
IN VARCHAR2,
5 p_oldprefix
IN VARCHAR2,
6 p_newprefix
IN VARCHAR2,
7 p_temporary
IN BOOLEAN) IS
8 e_table_not_exist
EXCEPTION;
9 PRAGMA
EXCEPTION_INIT(e_table_not_exist
, -00942);
10 l_temp_ei_sql
VARCHAR2(2000);
13 EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
14 ' CASCADE CONSTRAINTS';
16 WHEN e_table_not_exist
THEN
20 EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
21 p_tabname ||
' AS SELECT * FROM ' || p_oldprefix ||
22 p_tabname ||
' WHERE ROWNUM = 0';
24 EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname ||
25 ' AS SELECT * FROM ' || p_oldprefix || p_tabname ||
28 FOR rc
IN (SELECT column_name, data_default
30 WHERE table_name = p_oldprefix || p_tabname
31 AND data_default
IS NOT NULL) LOOP
32 EXECUTE IMMEDIATE 'ALTER TABLE ' || p_newprefix || p_tabname ||
33 ' MODIFY ' || rc.
column_name ||
' DEFAULT ' ||
34 SUBSTR(rc.data_default
, 1, 2000);
36 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('CONSTRAINT',
40 USER ||
'"."' || p_oldprefix
,
41 USER ||
'"."' || p_newprefix
),
42 '"' ||
constraint_name ||
'"',
43 '"' || p_newprefix ||
constraint_name ||
'"') DDLVC2
,
45 FROM user_constraints uc
46 WHERE table_name = p_oldprefix || p_tabname
47 AND constraint_type
= 'P') LOOP
48 l_temp_ei_sql
:= SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'PCTFREE') - 1);
49 l_temp_ei_sql
:= SUBSTR(l_temp_ei_sql
, 1, INSTR(l_temp_ei_sql
, ')', INSTR(l_temp_ei_sql
, 'PRIMARY KEY')+1)+1);
50 EXECUTE IMMEDIATE l_temp_ei_sql
;
52 IF (NOT p_temporary
) THEN
53 FOR rc
IN (SELECT REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('REF_CONSTRAINT',
57 USER ||
'"."' || p_oldprefix
,
58 USER ||
'"."' || p_newprefix
) DDLVC2
,
60 FROM user_constraints uc
61 WHERE table_name = p_oldprefix || p_tabname
62 AND constraint_type
= 'R') LOOP
63 EXECUTE IMMEDIATE rc.ddlvc2
;
66 FOR rc
IN (SELECT REPLACE(REPLACE(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('INDEX',
70 USER ||
'"."' || p_oldprefix
,
71 USER ||
'"."' || p_newprefix
),
72 '"' || index_name ||
'"',
73 '"' || p_newprefix || index_name ||
'"') DDLVC2
,
77 WHERE table_name = p_oldprefix || p_tabname
78 AND index_type
NOT IN ('LOB', 'DOMAIN')
82 WHERE table_name = ui.
table_name
83 AND constraint_name = ui.index_name
)) LOOP
84 l_temp_ei_sql
:= SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'PCTFREE') - 1);
85 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);
86 EXECUTE IMMEDIATE l_temp_ei_sql
;
88 FOR rc
IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.
SUBSTR(DBMS_METADATA.
get_ddl('TRIGGER',
92 USER ||
'"."' || p_oldprefix
,
93 USER ||
'"."' || p_newprefix
),
94 ' ON ' || p_oldprefix || p_tabname
,
95 ' ON ' || p_newprefix || p_tabname
) DDLVC2
,
98 WHERE table_name = p_oldprefix || p_tabname
) LOOP
99 l_temp_ei_sql
:= SUBSTR(rc.ddlvc2
, 1, INSTR(rc.ddlvc2
, 'ALTER ') - 1);
100 dbms_output.
put_line(l_temp_ei_sql
);
101 EXECUTE IMMEDIATE l_temp_ei_sql
;
106 CREATE OR REPLACE TYPE GET_OUTPUT_TYPE
IS TABLE OF VARCHAR2(255);
109 CREATE OR REPLACE FUNCTION GET_OUTPUT_LINES
RETURN GET_OUTPUT_TYPE PIPELINED
AS
110 v_line
VARCHAR2(255);
111 v_status
INTEGER := 0;
115 DBMS_OUTPUT.
GET_LINE(v_line
, v_status
);
116 IF (v_status
= 0) THEN RETURN; END IF;