[Summary view] [Print] [Text view]

   1  define mw_prefix='{$wgDBprefix}';
   2  
   3  /*$mw$*/
   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);
  11  BEGIN
  12    BEGIN
  13      EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
  14                        ' CASCADE CONSTRAINTS';
  15    EXCEPTION
  16      WHEN e_table_not_exist THEN
  17        NULL;
  18    END;
  19    IF (p_temporary) THEN
  20      EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
  21                        p_tabname || ' AS SELECT * FROM ' || p_oldprefix ||
  22                        p_tabname || ' WHERE ROWNUM = 0';
  23    ELSE
  24      EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname ||
  25                        ' AS SELECT * FROM ' || p_oldprefix || p_tabname ||
  26                        ' WHERE ROWNUM = 0';
  27    END IF;
  28    FOR rc IN (SELECT column_name, data_default
  29                 FROM user_tab_columns
  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);
  35    END LOOP;
  36    FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('CONSTRAINT',
  37                                                                            constraint_name),
  38                                                      32767,
  39                                                      1),
  40                                      USER || '"."' || p_oldprefix,
  41                                      USER || '"."' || p_newprefix),
  42                              '"' || constraint_name || '"',
  43                              '"' || p_newprefix || constraint_name || '"') DDLVC2,
  44                      constraint_name
  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;
  51    END LOOP;
  52    IF (NOT p_temporary) THEN
  53    FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT',
  54                                                                    constraint_name),
  55                                              32767,
  56                                              1),
  57                              USER || '"."' || p_oldprefix,
  58                              USER || '"."' || p_newprefix) DDLVC2,
  59                      constraint_name
  60                 FROM user_constraints uc
  61                WHERE table_name = p_oldprefix || p_tabname
  62                  AND constraint_type = 'R') LOOP
  63      EXECUTE IMMEDIATE rc.ddlvc2;
  64    END LOOP;
  65    END IF;
  66    FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
  67                                                                            index_name),
  68                                                      32767,
  69                                                      1),
  70                                      USER || '"."' || p_oldprefix,
  71                                      USER || '"."' || p_newprefix),
  72                              '"' || index_name || '"',
  73                              '"' || p_newprefix || index_name || '"') DDLVC2,
  74                      index_name,
  75                      index_type
  76                 FROM user_indexes ui
  77                WHERE table_name = p_oldprefix || p_tabname
  78                  AND index_type NOT IN ('LOB', 'DOMAIN')
  79                  AND NOT EXISTS
  80                (SELECT NULL
  81                         FROM user_constraints
  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;
  87    END LOOP;
  88    FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER',
  89                                                                                  trigger_name),
  90                                                            32767,
  91                                                            1)),
  92                                      USER || '"."' || p_oldprefix,
  93                                      USER || '"."' || p_newprefix),
  94                              ' ON ' || p_oldprefix || p_tabname,
  95                              ' ON ' || p_newprefix || p_tabname) DDLVC2,
  96                      trigger_name
  97                 FROM user_triggers
  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;
 102    END LOOP;
 103  END;
 104  /*$mw$*/
 105  
 106  CREATE OR REPLACE TYPE GET_OUTPUT_TYPE IS TABLE OF VARCHAR2(255);
 107  
 108  /*$mw$*/
 109  CREATE OR REPLACE FUNCTION GET_OUTPUT_LINES RETURN GET_OUTPUT_TYPE PIPELINED AS
 110    v_line VARCHAR2(255);
 111    v_status INTEGER := 0;
 112  BEGIN
 113  
 114    LOOP
 115      DBMS_OUTPUT.GET_LINE(v_line, v_status);
 116      IF (v_status = 0) THEN RETURN; END IF;
 117      PIPE ROW (v_line);
 118    END LOOP;
 119    RETURN;
 120  EXCEPTION
 121    WHEN OTHERS THEN
 122      RETURN;
 123  END;
 124  /*$mw$*/
 125