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