[Summary view] [Print] [Text view]

   1  /*$mw$*/
   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;
  10  BEGIN
  11    BEGIN
  12      EXECUTE IMMEDIATE 'DROP TABLE ' || p_newprefix || p_tabname ||
  13                        ' CASCADE CONSTRAINTS PURGE';
  14    EXCEPTION
  15      WHEN e_table_not_exist THEN
  16        NULL;
  17    END;
  18    IF (p_tabname = 'SEARCHINDEX') THEN
  19      l_temporary := FALSE;
  20    END IF;
  21    IF (l_temporary) THEN
  22      EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ' || p_newprefix ||
  23                        p_tabname ||
  24                        ' ON COMMIT PRESERVE ROWS AS SELECT * FROM ' ||
  25                        p_oldprefix || p_tabname || ' WHERE ROWNUM = 0';
  26    ELSE
  27      EXECUTE IMMEDIATE 'CREATE TABLE ' || p_newprefix || p_tabname ||
  28                        ' AS SELECT * FROM ' || p_oldprefix || p_tabname ||
  29                        ' WHERE ROWNUM = 0';
  30    END IF;
  31    FOR rc IN (SELECT column_name, data_default
  32                 FROM user_tab_columns
  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);
  38    END LOOP;
  39    FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('CONSTRAINT',
  40                                                                            constraint_name),
  41                                                      32767,
  42                                                      1),
  43                                      USER || '"."' || p_oldprefix,
  44                                      USER || '"."' || p_newprefix),
  45                              '"' || constraint_name || '"',
  46                              '"' || p_newprefix || constraint_name || '"') DDLVC2,
  47                      constraint_name
  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,
  53                              1,
  54                              INSTR(l_temp_ei_sql,
  55                                    ')',
  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;
  59      END IF;
  60    END LOOP;
  61    IF (NOT l_temporary) THEN
  62      FOR rc IN (SELECT REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('REF_CONSTRAINT',
  63                                                                      constraint_name),
  64                                                32767,
  65                                                1),
  66                                USER || '"."' || p_oldprefix,
  67                                USER || '"."' || p_newprefix) DDLVC2,
  68                        constraint_name
  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;
  75        END IF;
  76      END LOOP;
  77    END IF;
  78    FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
  79                                                                            index_name),
  80                                                      32767,
  81                                                      1),
  82                                      USER || '"."' || p_oldprefix,
  83                                      USER || '"."' || p_newprefix),
  84                              '"' || index_name || '"',
  85                              '"' || p_newprefix || index_name || '"') DDLVC2,
  86                      index_name,
  87                      index_type
  88                 FROM user_indexes ui
  89                WHERE table_name = p_oldprefix || p_tabname
  90                  AND index_type NOT IN ('LOB', 'DOMAIN')
  91                  AND NOT EXISTS
  92                (SELECT NULL
  93                         FROM user_constraints
  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,
  98                              1,
  99                              INSTR(l_temp_ei_sql,
 100                                    ')',
 101                                    INSTR(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;
 105      END IF;
 106    END LOOP;
 107    FOR rc IN (SELECT REPLACE(REPLACE(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('INDEX',
 108                                                                            index_name),
 109                                                      32767,
 110                                                      1),
 111                                      USER || '"."' || p_oldprefix,
 112                                      USER || '"."' || p_newprefix),
 113                              '"' || index_name || '"',
 114                              '"' || p_newprefix || index_name || '"') DDLVC2,
 115                      index_name,
 116                      index_type
 117                 FROM user_indexes ui
 118                WHERE table_name = p_oldprefix || p_tabname
 119                  AND index_type = 'DOMAIN'
 120                  AND NOT EXISTS
 121                (SELECT NULL
 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;
 128      END IF;
 129    END LOOP;
 130    FOR rc IN (SELECT REPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.get_ddl('TRIGGER',
 131                                                                                  trigger_name),
 132                                                            32767,
 133                                                            1)),
 134                                      USER || '"."' || p_oldprefix,
 135                                      USER || '"."' || p_newprefix),
 136                              ' ON ' || p_oldprefix || p_tabname,
 137                              ' ON ' || p_newprefix || p_tabname) DDLVC2,
 138                      trigger_name
 139                 FROM user_triggers
 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;
 144      END IF;
 145    END LOOP;
 146  END;
 147  
 148  /*$mw$*/
 149