database/model/postscript-Oracle.sql
author Carlos Romero <carlos.romero@openbravo.com>
Mon, 03 Mar 2008 13:53:54 +0000
changeset 497 6b35085fdfe9
parent 496 feb9bbf5b22e
child 506 0d5dbfc88635
permissions -rw-r--r--
Now function excluded from standard openbravo functions and added to the Oracle postscript
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
carlos@497
    38
CREATE function NOW
carlos@497
    39
RETURN DATE
carlos@497
    40
AS
carlos@497
    41
BEGIN
carlos@497
    42
  RETURN SYSDATE;
carlos@497
    43
END NOW;
carlos@497
    44
/-- END NOW
carlos@497
    45
adrian@101
    46
carlos@496
    47
CREATE OR REPLACE FUNCTION ad_script_disable_triggers (p_seqNoStart NUMBER)
carlos@496
    48
  RETURN NUMBER
carlos@496
    49
AS
carlos@496
    50
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    51
 TYPE RECORD IS REF CURSOR;
carlos@496
    52
 Cur_Triggers RECORD;
carlos@496
    53
BEGIN
carlos@496
    54
     FOR Cur_Triggers IN 
carlos@496
    55
      (SELECT OBJECT_NAME AS NAME 
carlos@496
    56
      FROM USER_OBJECTS 
carlos@496
    57
      WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
    58
      ORDER BY OBJECT_NAME 
carlos@496
    59
      ) 
carlos@496
    60
    LOOP 
carlos@496
    61
      v_seqNo:=v_seqNo + 1; 
carlos@496
    62
      INSERT 
carlos@496
    63
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    64
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' DISABLE') ; 
carlos@496
    65
    END LOOP; 
carlos@496
    66
carlos@496
    67
 RETURN v_seqNo;
carlos@496
    68
END ad_script_disable_triggers;
carlos@496
    69
/-- END
carlos@496
    70
carlos@496
    71
CREATE OR REPLACE FUNCTION ad_script_disable_constraints(p_seqNoStart NUMBER)
carlos@496
    72
  RETURN NUMBER
carlos@496
    73
AS
carlos@496
    74
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    75
 TYPE RECORD IS REF CURSOR;
carlos@496
    76
 Cur_Constraints RECORD;
carlos@496
    77
BEGIN
carlos@496
    78
    FOR Cur_Constraints IN 
carlos@496
    79
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
    80
    FROM USER_CONSTRAINTS C1 
carlos@496
    81
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND DELETE_RULE NOT LIKE 'CASCADE' 
carlos@496
    82
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 1 WHEN 'U' THEN 2 WHEN 'P' THEN 3 
carlos@496
    83
      END 
carlos@496
    84
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
    85
    LOOP 
carlos@496
    86
      v_seqNo:=v_seqNo + 1; 
carlos@496
    87
      INSERT 
carlos@496
    88
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    89
        (v_seqNo, 'ALTER TABLE '||Cur_Constraints.TABLE_NAME||' DISABLE CONSTRAINT '||Cur_Constraints.CONSTRAINT_NAME) ; 
carlos@496
    90
    END LOOP; 
carlos@496
    91
carlos@496
    92
 RETURN v_seqNo;
carlos@496
    93
END ad_script_disable_constraints;
carlos@496
    94
/-- END
carlos@496
    95
carlos@496
    96
carlos@496
    97
CREATE OR REPLACE FUNCTION ad_script_enable_triggers(p_seqNoStart NUMBER)
carlos@496
    98
  RETURN NUMBER 
carlos@496
    99
AS
carlos@496
   100
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
   101
 TYPE RECORD IS REF CURSOR;
carlos@496
   102
 Cur_Triggers RECORD;
carlos@496
   103
BEGIN
carlos@496
   104
    FOR Cur_Triggers IN 
carlos@496
   105
      (SELECT OBJECT_NAME AS NAME 
carlos@496
   106
    FROM USER_OBJECTS 
carlos@496
   107
    WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
   108
    ORDER BY OBJECT_NAME) 
carlos@496
   109
    LOOP 
carlos@496
   110
      v_seqNo:=v_seqNo + 1; 
carlos@496
   111
      INSERT 
carlos@496
   112
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   113
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' ENABLE') ; 
carlos@496
   114
    END LOOP; 
carlos@496
   115
carlos@496
   116
 RETURN v_seqNo;
carlos@496
   117
END ad_script_enable_triggers;
carlos@496
   118
/-- END
carlos@496
   119
carlos@496
   120
CREATE OR REPLACE FUNCTION ad_script_enable_constraints(p_seqNoStart NUMBER)
carlos@496
   121
  RETURN NUMBER
carlos@496
   122
AS
carlos@496
   123
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
   124
 TYPE RECORD IS REF CURSOR;
carlos@496
   125
 Cur_Constraints RECORD;
carlos@496
   126
BEGIN
carlos@496
   127
    FOR Cur_ConstraintsEnable IN 
carlos@496
   128
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
   129
    FROM USER_CONSTRAINTS C1 
carlos@496
   130
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND DELETE_RULE NOT LIKE 'CASCADE' 
carlos@496
   131
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 3 WHEN 'U' THEN 2 WHEN 'P' THEN 1 
carlos@496
   132
      END 
carlos@496
   133
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
   134
    LOOP 
carlos@496
   135
      v_seqNo:=v_seqNo + 1; 
carlos@496
   136
      INSERT 
carlos@496
   137
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   138
        (v_seqNo, 'ALTER TABLE '||Cur_ConstraintsEnable.TABLE_NAME||' ENABLE CONSTRAINT '||Cur_ConstraintsEnable.CONSTRAINT_NAME) ; 
carlos@496
   139
    END LOOP; 
carlos@496
   140
carlos@496
   141
  RETURN v_seqNo;
carlos@496
   142
END ad_script_enable_constraints;
carlos@496
   143
/-- END
carlos@496
   144
carlos@496
   145
carlos@496
   146
CREATE OR REPLACE FUNCTION ad_script_execute (param_Message VARCHAR2)
carlos@496
   147
  RETURN VARCHAR2
carlos@496
   148
AS
carlos@496
   149
 v_Message       VARCHAR2(4000) := '';
carlos@496
   150
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   151
 TYPE RECORD IS REF CURSOR;
carlos@496
   152
 Cur_Script RECORD;
carlos@496
   153
BEGIN
carlos@496
   154
    v_Message := param_Message;
carlos@496
   155
    FOR Cur_Script IN 
carlos@496
   156
      (SELECT STRSQL, SEQNO FROM AD_SCRIPT_SQL ORDER BY SEQNO)
carlos@496
   157
    LOOP 
carlos@496
   158
    BEGIN 
carlos@496
   159
      EXECUTE IMMEDIATE(Cur_Script.STRSQL) ; 
carlos@496
   160
    EXCEPTION 
carlos@496
   161
    WHEN OTHERS THEN 
carlos@496
   162
      
carlos@496
   163
      if (coalesce(length(v_Message),0)!=0) then
carlos@496
   164
        v_Message:=substr(v_Message||'<br><br>',1,2000);
carlos@496
   165
      end if;
carlos@496
   166
      v_Message := substr(v_Message||'@SQLScriptError@ '||Cur_Script.SeqNo||'. @Executing@'||Cur_Script.strSQL||'<br>'||SQLERRM,1,2000);
carlos@496
   167
    END;
carlos@496
   168
  END LOOP;
carlos@496
   169
carlos@496
   170
 IF( LENGTH(v_Message) > 0 ) THEN
carlos@496
   171
    DBMS_OUTPUT.PUT_LINE('Script errors: ' || v_Message);
carlos@496
   172
 END IF;
carlos@496
   173
 return substr(coalesce(v_ResultStr,'') || coalesce(v_Message,''), 1, 2000);
carlos@496
   174
END ad_script_execute;
carlos@496
   175
/-- END
carlos@496
   176
carlos@496
   177
CREATE OR REPLACE FUNCTION ad_script_drop_recreate_index (p_seqNoStart NUMBER)
carlos@496
   178
  RETURN NUMBER
carlos@496
   179
AS
carlos@496
   180
 v_seqNo         NUMBER; 
carlos@496
   181
 v_strSql        VARCHAR2(4000) := '';
carlos@496
   182
 v_strTemp       VARCHAR2(4000) := '';
carlos@496
   183
 v_Message       VARCHAR2(4000) := '';
carlos@496
   184
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   185
 TYPE RECORD IS REF CURSOR;
carlos@496
   186
 Cur_UniqueIndex  RECORD;
carlos@496
   187
 Cur_IndexColumns RECORD;
carlos@496
   188
BEGIN
carlos@496
   189
    v_seqNo := p_seqNoStart;
carlos@496
   190
    FOR Cur_UniqueIndex IN (SELECT i.INDEX_NAME, i.TABLE_NAME, i.TABLESPACE_NAME, CONSTRAINT_TYPE
carlos@496
   191
                 FROM USER_INDEXES I left join USER_CONSTRAINTS C1 on c1.INDEX_NAME=I.INDEX_NAME
carlos@496
   192
                 WHERE UNIQUENESS='UNIQUE' AND INDEX_TYPE='NORMAL' AND TABLE_TYPE='TABLE'
carlos@496
   193
               --AND CONSTRAINT_TYPE != 'U'
carlos@496
   194
               ORDER BY INDEX_NAME)
carlos@496
   195
carlos@496
   196
    LOOP
carlos@496
   197
      v_seqNo:=v_seqNo + 1;
carlos@496
   198
      INSERT
carlos@496
   199
      INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'DROP INDEX '||Cur_UniqueIndex.INDEX_NAME) ;
carlos@496
   200
carlos@496
   201
   IF Cur_UniqueIndex.CONSTRAINT_TYPE != 'P' THEN
carlos@496
   202
    v_strSql:='CREATE INDEX '||Cur_UniqueIndex.INDEX_NAME||' ON '||Cur_UniqueIndex.TABLE_NAME||'(';
carlos@496
   203
       v_strTemp:='';
carlos@496
   204
       FOR Cur_IndexColumns IN
carlos@496
   205
         (SELECT COLUMN_NAME
carlos@496
   206
       FROM USER_IND_COLUMNS
carlos@496
   207
       WHERE INDEX_NAME=Cur_UniqueIndex.INDEX_NAME
carlos@496
   208
       ORDER BY COLUMN_POSITION)
carlos@496
   209
       LOOP
carlos@496
   210
         v_strTemp:=v_strTemp ||','|| Cur_IndexColumns.COLUMN_NAME;
carlos@496
   211
       END LOOP;
carlos@496
   212
       v_strSql:=v_strSql || SUBSTR(v_strTemp, 2, 2000) || ') TABLESPACE '||Cur_UniqueIndex.TABLESPACE_NAME;
carlos@496
   213
       INSERT INTO AD_SCRIPT_SQL VALUES(v_seqNo+100000, v_strSql) ;
carlos@496
   214
   END IF;
carlos@496
   215
 END LOOP;
carlos@496
   216
 return v_seqNo;
carlos@496
   217
END ad_script_drop_recreate_index;
carlos@496
   218
/-- END
carlos@496
   219
carlos@496
   220
carlos@0
   221
CALL DBA_RECOMPILE(NULL)
carlos@0
   222
/-- END
carlos@0
   223