database/model/postscript-Oracle.sql
author Carlos Romero <carlos.romero@openbravo.com>
Mon, 03 Mar 2008 13:47:58 +0000
changeset 496 feb9bbf5b22e
parent 101 7aea4257c160
child 497 6b35085fdfe9
permissions -rw-r--r--
Todo task done [ 1906267 ] Unify AD_DELETE_CLIENT and AD_COPY_CLIENT
adrian@101
     1
-- ----------------------------------------------------------------------- 
adrian@101
     2
-- TABLE PLAN_TABLE 
adrian@101
     3
-- ----------------------------------------------------------------------- 
adrian@101
     4
CREATE TABLE PLAN_TABLE
adrian@101
     5
(
adrian@101
     6
    STATEMENT_ID VARCHAR2(30),
adrian@101
     7
    TIMESTAMP DATE,
adrian@101
     8
    REMARKS VARCHAR2(80),
adrian@101
     9
    OPERATION VARCHAR2(30),
adrian@101
    10
    OPTIONS VARCHAR2(255),
adrian@101
    11
    OBJECT_NODE VARCHAR2(128),
adrian@101
    12
    OBJECT_OWNER VARCHAR2(30),
adrian@101
    13
    OBJECT_NAME VARCHAR2(30),
adrian@101
    14
    OBJECT_INSTANCE INTEGER,
adrian@101
    15
    OBJECT_TYPE VARCHAR2(30),
adrian@101
    16
    OPTIMIZER VARCHAR2(255),
adrian@101
    17
    SEARCH_COLUMNS NUMBER,
adrian@101
    18
    ID INTEGER,
adrian@101
    19
    PARENT_ID INTEGER,
adrian@101
    20
    POSITION INTEGER,
adrian@101
    21
    COST INTEGER,
adrian@101
    22
    CARDINALITY INTEGER,
adrian@101
    23
    BYTES INTEGER,
adrian@101
    24
    OTHER_TAG VARCHAR2(255),
adrian@101
    25
    PARTITION_START VARCHAR2(255),
adrian@101
    26
    PARTITION_STOP VARCHAR2(255),
adrian@101
    27
    PARTITION_ID INTEGER,
adrian@101
    28
    OTHER CLOB,
adrian@101
    29
    DISTRIBUTION VARCHAR2(30),
adrian@101
    30
    CPU_COST INTEGER,
adrian@101
    31
    IO_COST INTEGER,
adrian@101
    32
    TEMP_SPACE INTEGER,
adrian@101
    33
    ACCESS_PREDICATES VARCHAR2(4000),
adrian@101
    34
    FILTER_PREDICATES VARCHAR2(4000)
adrian@101
    35
)
adrian@101
    36
/-- END PLAN_TABLE 
adrian@101
    37
adrian@101
    38
carlos@496
    39
CREATE OR REPLACE FUNCTION ad_script_disable_triggers (p_seqNoStart NUMBER)
carlos@496
    40
  RETURN NUMBER
carlos@496
    41
AS
carlos@496
    42
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    43
 TYPE RECORD IS REF CURSOR;
carlos@496
    44
 Cur_Triggers RECORD;
carlos@496
    45
BEGIN
carlos@496
    46
     FOR Cur_Triggers IN 
carlos@496
    47
      (SELECT OBJECT_NAME AS NAME 
carlos@496
    48
      FROM USER_OBJECTS 
carlos@496
    49
      WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
    50
      ORDER BY OBJECT_NAME 
carlos@496
    51
      ) 
carlos@496
    52
    LOOP 
carlos@496
    53
      v_seqNo:=v_seqNo + 1; 
carlos@496
    54
      INSERT 
carlos@496
    55
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    56
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' DISABLE') ; 
carlos@496
    57
    END LOOP; 
carlos@496
    58
carlos@496
    59
 RETURN v_seqNo;
carlos@496
    60
END ad_script_disable_triggers;
carlos@496
    61
/-- END
carlos@496
    62
carlos@496
    63
CREATE OR REPLACE FUNCTION ad_script_disable_constraints(p_seqNoStart NUMBER)
carlos@496
    64
  RETURN NUMBER
carlos@496
    65
AS
carlos@496
    66
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    67
 TYPE RECORD IS REF CURSOR;
carlos@496
    68
 Cur_Constraints RECORD;
carlos@496
    69
BEGIN
carlos@496
    70
    FOR Cur_Constraints IN 
carlos@496
    71
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
    72
    FROM USER_CONSTRAINTS C1 
carlos@496
    73
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND DELETE_RULE NOT LIKE 'CASCADE' 
carlos@496
    74
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 1 WHEN 'U' THEN 2 WHEN 'P' THEN 3 
carlos@496
    75
      END 
carlos@496
    76
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
    77
    LOOP 
carlos@496
    78
      v_seqNo:=v_seqNo + 1; 
carlos@496
    79
      INSERT 
carlos@496
    80
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    81
        (v_seqNo, 'ALTER TABLE '||Cur_Constraints.TABLE_NAME||' DISABLE CONSTRAINT '||Cur_Constraints.CONSTRAINT_NAME) ; 
carlos@496
    82
    END LOOP; 
carlos@496
    83
carlos@496
    84
 RETURN v_seqNo;
carlos@496
    85
END ad_script_disable_constraints;
carlos@496
    86
/-- END
carlos@496
    87
carlos@496
    88
carlos@496
    89
CREATE OR REPLACE FUNCTION ad_script_enable_triggers(p_seqNoStart NUMBER)
carlos@496
    90
  RETURN NUMBER 
carlos@496
    91
AS
carlos@496
    92
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    93
 TYPE RECORD IS REF CURSOR;
carlos@496
    94
 Cur_Triggers RECORD;
carlos@496
    95
BEGIN
carlos@496
    96
    FOR Cur_Triggers IN 
carlos@496
    97
      (SELECT OBJECT_NAME AS NAME 
carlos@496
    98
    FROM USER_OBJECTS 
carlos@496
    99
    WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
   100
    ORDER BY OBJECT_NAME) 
carlos@496
   101
    LOOP 
carlos@496
   102
      v_seqNo:=v_seqNo + 1; 
carlos@496
   103
      INSERT 
carlos@496
   104
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   105
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' ENABLE') ; 
carlos@496
   106
    END LOOP; 
carlos@496
   107
carlos@496
   108
 RETURN v_seqNo;
carlos@496
   109
END ad_script_enable_triggers;
carlos@496
   110
/-- END
carlos@496
   111
carlos@496
   112
CREATE OR REPLACE FUNCTION ad_script_enable_constraints(p_seqNoStart NUMBER)
carlos@496
   113
  RETURN NUMBER
carlos@496
   114
AS
carlos@496
   115
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
   116
 TYPE RECORD IS REF CURSOR;
carlos@496
   117
 Cur_Constraints RECORD;
carlos@496
   118
BEGIN
carlos@496
   119
    FOR Cur_ConstraintsEnable IN 
carlos@496
   120
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
   121
    FROM USER_CONSTRAINTS C1 
carlos@496
   122
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND DELETE_RULE NOT LIKE 'CASCADE' 
carlos@496
   123
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 3 WHEN 'U' THEN 2 WHEN 'P' THEN 1 
carlos@496
   124
      END 
carlos@496
   125
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
   126
    LOOP 
carlos@496
   127
      v_seqNo:=v_seqNo + 1; 
carlos@496
   128
      INSERT 
carlos@496
   129
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   130
        (v_seqNo, 'ALTER TABLE '||Cur_ConstraintsEnable.TABLE_NAME||' ENABLE CONSTRAINT '||Cur_ConstraintsEnable.CONSTRAINT_NAME) ; 
carlos@496
   131
    END LOOP; 
carlos@496
   132
carlos@496
   133
  RETURN v_seqNo;
carlos@496
   134
END ad_script_enable_constraints;
carlos@496
   135
/-- END
carlos@496
   136
carlos@496
   137
carlos@496
   138
CREATE OR REPLACE FUNCTION ad_script_execute (param_Message VARCHAR2)
carlos@496
   139
  RETURN VARCHAR2
carlos@496
   140
AS
carlos@496
   141
 v_Message       VARCHAR2(4000) := '';
carlos@496
   142
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   143
 TYPE RECORD IS REF CURSOR;
carlos@496
   144
 Cur_Script RECORD;
carlos@496
   145
BEGIN
carlos@496
   146
    v_Message := param_Message;
carlos@496
   147
    FOR Cur_Script IN 
carlos@496
   148
      (SELECT STRSQL, SEQNO FROM AD_SCRIPT_SQL ORDER BY SEQNO)
carlos@496
   149
    LOOP 
carlos@496
   150
    BEGIN 
carlos@496
   151
      EXECUTE IMMEDIATE(Cur_Script.STRSQL) ; 
carlos@496
   152
    EXCEPTION 
carlos@496
   153
    WHEN OTHERS THEN 
carlos@496
   154
      
carlos@496
   155
      if (coalesce(length(v_Message),0)!=0) then
carlos@496
   156
        v_Message:=substr(v_Message||'<br><br>',1,2000);
carlos@496
   157
      end if;
carlos@496
   158
      v_Message := substr(v_Message||'@SQLScriptError@ '||Cur_Script.SeqNo||'. @Executing@'||Cur_Script.strSQL||'<br>'||SQLERRM,1,2000);
carlos@496
   159
    END;
carlos@496
   160
  END LOOP;
carlos@496
   161
carlos@496
   162
 IF( LENGTH(v_Message) > 0 ) THEN
carlos@496
   163
    DBMS_OUTPUT.PUT_LINE('Script errors: ' || v_Message);
carlos@496
   164
 END IF;
carlos@496
   165
 return substr(coalesce(v_ResultStr,'') || coalesce(v_Message,''), 1, 2000);
carlos@496
   166
END ad_script_execute;
carlos@496
   167
/-- END
carlos@496
   168
carlos@496
   169
CREATE OR REPLACE FUNCTION ad_script_drop_recreate_index (p_seqNoStart NUMBER)
carlos@496
   170
  RETURN NUMBER
carlos@496
   171
AS
carlos@496
   172
 v_seqNo         NUMBER; 
carlos@496
   173
 v_strSql        VARCHAR2(4000) := '';
carlos@496
   174
 v_strTemp       VARCHAR2(4000) := '';
carlos@496
   175
 v_Message       VARCHAR2(4000) := '';
carlos@496
   176
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   177
 TYPE RECORD IS REF CURSOR;
carlos@496
   178
 Cur_UniqueIndex  RECORD;
carlos@496
   179
 Cur_IndexColumns RECORD;
carlos@496
   180
BEGIN
carlos@496
   181
    v_seqNo := p_seqNoStart;
carlos@496
   182
    FOR Cur_UniqueIndex IN (SELECT i.INDEX_NAME, i.TABLE_NAME, i.TABLESPACE_NAME, CONSTRAINT_TYPE
carlos@496
   183
                 FROM USER_INDEXES I left join USER_CONSTRAINTS C1 on c1.INDEX_NAME=I.INDEX_NAME
carlos@496
   184
                 WHERE UNIQUENESS='UNIQUE' AND INDEX_TYPE='NORMAL' AND TABLE_TYPE='TABLE'
carlos@496
   185
               --AND CONSTRAINT_TYPE != 'U'
carlos@496
   186
               ORDER BY INDEX_NAME)
carlos@496
   187
carlos@496
   188
    LOOP
carlos@496
   189
      v_seqNo:=v_seqNo + 1;
carlos@496
   190
      INSERT
carlos@496
   191
      INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'DROP INDEX '||Cur_UniqueIndex.INDEX_NAME) ;
carlos@496
   192
carlos@496
   193
   IF Cur_UniqueIndex.CONSTRAINT_TYPE != 'P' THEN
carlos@496
   194
    v_strSql:='CREATE INDEX '||Cur_UniqueIndex.INDEX_NAME||' ON '||Cur_UniqueIndex.TABLE_NAME||'(';
carlos@496
   195
       v_strTemp:='';
carlos@496
   196
       FOR Cur_IndexColumns IN
carlos@496
   197
         (SELECT COLUMN_NAME
carlos@496
   198
       FROM USER_IND_COLUMNS
carlos@496
   199
       WHERE INDEX_NAME=Cur_UniqueIndex.INDEX_NAME
carlos@496
   200
       ORDER BY COLUMN_POSITION)
carlos@496
   201
       LOOP
carlos@496
   202
         v_strTemp:=v_strTemp ||','|| Cur_IndexColumns.COLUMN_NAME;
carlos@496
   203
       END LOOP;
carlos@496
   204
       v_strSql:=v_strSql || SUBSTR(v_strTemp, 2, 2000) || ') TABLESPACE '||Cur_UniqueIndex.TABLESPACE_NAME;
carlos@496
   205
       INSERT INTO AD_SCRIPT_SQL VALUES(v_seqNo+100000, v_strSql) ;
carlos@496
   206
   END IF;
carlos@496
   207
 END LOOP;
carlos@496
   208
 return v_seqNo;
carlos@496
   209
END ad_script_drop_recreate_index;
carlos@496
   210
/-- END
carlos@496
   211
carlos@496
   212
carlos@0
   213
CALL DBA_RECOMPILE(NULL)
carlos@0
   214
/-- END
carlos@0
   215