maintenance/oracle/archives/patch_rebuild_dupfunc.sql

   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