maintenance/oracle/archives/patch_create_17_functions.sql

   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