database/model/postscript-Oracle.sql
author Antonio Moreno <antonio.moreno@openbravo.com>
Thu, 06 Mar 2008 15:15:49 +0000
changeset 506 0d5dbfc88635
parent 497 6b35085fdfe9
child 665 cb13d0d7a497
permissions -rw-r--r--
Moved procedures DBA_AFTERIMPORT and DBA_RECOMPILE 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
antonio@506
   220
CREATE OR REPLACE PROCEDURE DBA_RECOMPILE(p_PInstance_ID IN NUMBER) 
antonio@506
   221
AS
antonio@506
   222
/*************************************************************************
antonio@506
   223
  * The contents of this file are subject to the Compiere License
antonio@506
   224
  * Version 2.5.0 ("License"); You may not use this file except in
antonio@506
   225
  * compliance with the License. You may obtain a copy of the License at
antonio@506
   226
  * http://www.compiere.org/license.html
antonio@506
   227
  * Software distributed under the License is distributed on an
antonio@506
   228
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   229
  * implied. See the License for the specific language governing rights
antonio@506
   230
  * and limitations under the License.
antonio@506
   231
  * The Original Code is  Compiere  ERP &  Business Solution
antonio@506
   232
  * The Initial Developer of the Original Code is Jorg Janke  and ComPiere,
antonio@506
   233
  Inc.
antonio@506
   234
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   235
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   236
  * All Rights Reserved.
antonio@506
   237
  * Contributor(s): Openbravo SL
antonio@506
   238
  * Contributions are Copyright (C) 1999-2005 Openbravo, S.L
antonio@506
   239
  *************************************************************************
antonio@506
   240
  * $Id: DBA_Recompile.sql,v 1.7 2003/03/14 06:11:21 jjanke Exp $
antonio@506
   241
  ***
antonio@506
   242
  * Title:  Recompile all User_Objects
antonio@506
   243
  * Description:
antonio@506
   244
  ************************************************************************/
antonio@506
   245
  -- Logistice
antonio@506
   246
  v_Message VARCHAR2(2000):=' ';
antonio@506
   247
  v_Result NUMBER:=1; --  0=failure
antonio@506
   248
  --
antonio@506
   249
  v_Buffer VARCHAR2(2000):='';
antonio@506
   250
  v_Line VARCHAR(100) ;
antonio@506
   251
  v_PrintInfo CHAR(1):='N'; -- Diagnostic
antonio@506
   252
  --
antonio@506
   253
  CURSOR Cur_Invalids IS
antonio@506
   254
    SELECT object_id,
antonio@506
   255
      object_name,
antonio@506
   256
      object_type
antonio@506
   257
    FROM user_objects
antonio@506
   258
    WHERE status<>'VALID'
antonio@506
   259
      AND object_type IN('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA CLASS')
antonio@506
   260
    ORDER BY object_type,
antonio@506
   261
      object_name;
antonio@506
   262
    CURSOR Cur_Valids (p_id NUMBER) IS
antonio@506
   263
      SELECT 'FOUND'  FROM user_objects  WHERE status='VALID'  AND object_id=p_id;
antonio@506
   264
      --  failed compile
antonio@506
   265
    TYPE invalid_tab IS TABLE OF Cur_Invalids%ROWTYPE INDEX BY BINARY_INTEGER;
antonio@506
   266
    invalid_tab_rec invalid_tab;
antonio@506
   267
    count_compiled PLS_INTEGER;
antonio@506
   268
    valid_text VARCHAR2(5) ;
antonio@506
   269
    exec_cursor PLS_INTEGER:=DBMS_SQL.OPEN_CURSOR;
antonio@506
   270
    sql_statement VARCHAR2(200) ;
antonio@506
   271
    count_object PLS_INTEGER:=0;
antonio@506
   272
  BEGIN
antonio@506
   273
    LOOP
antonio@506
   274
      count_compiled:=0;
antonio@506
   275
      FOR ci IN Cur_Invalids
antonio@506
   276
      LOOP
antonio@506
   277
        --  not unsuccessfuly compiled yet
antonio@506
   278
        IF NOT invalid_tab_rec.EXISTS(ci.object_id) THEN
antonio@506
   279
          IF(ci.object_type='JAVA CLASS') THEN
antonio@506
   280
            sql_statement:='ALTER JAVA CLASS "' || ci.object_name || '" RESOLVE';
antonio@506
   281
          ELSIF(ci.object_type='PACKAGE BODY') THEN
antonio@506
   282
            sql_statement:='ALTER PACKAGE ' || ci.object_name || ' COMPILE BODY';
antonio@506
   283
          ELSE
antonio@506
   284
            sql_statement:='ALTER ' || ci.object_type || ' ' || ci.object_name || ' COMPILE';
antonio@506
   285
          END IF;
antonio@506
   286
          --  compile
antonio@506
   287
        BEGIN
antonio@506
   288
          count_object:=count_object + 1;
antonio@506
   289
          DBMS_SQL.PARSE(exec_cursor, sql_statement, DBMS_SQL.NATIVE) ;
antonio@506
   290
        EXCEPTION
antonio@506
   291
        WHEN OTHERS THEN
antonio@506
   292
          NULL;
antonio@506
   293
        END;
antonio@506
   294
        --
antonio@506
   295
        OPEN Cur_Valids(ci.object_ID) ;
antonio@506
   296
        FETCH Cur_Valids INTO valid_text;
antonio@506
   297
        IF Cur_Valids%ROWCOUNT>0 THEN
antonio@506
   298
          IF(v_PrintInfo='Y') THEN
antonio@506
   299
            DBMS_OUTPUT.PUT_LINE('OK: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   300
          END IF;
antonio@506
   301
          count_compiled:=count_compiled + 1;
antonio@506
   302
          CLOSE Cur_Valids;
antonio@506
   303
          EXIT;
antonio@506
   304
        ELSE
antonio@506
   305
          IF(LENGTH(v_Message)<1950) THEN
antonio@506
   306
            v_Message:=v_Message || ci.object_name || ' ';
antonio@506
   307
          END IF;
antonio@506
   308
          IF(v_PrintInfo='Y') THEN
antonio@506
   309
            DBMS_OUTPUT.PUT_LINE('Error: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   310
          END IF;
antonio@506
   311
          --
antonio@506
   312
          invalid_tab_rec(ci.object_id) .object_name:=ci.object_name;
antonio@506
   313
          invalid_tab_rec(ci.object_id) .object_type:=ci.object_type;
antonio@506
   314
          CLOSE Cur_Valids;
antonio@506
   315
        END IF;
antonio@506
   316
      END IF; -- not unsuccessfuly compiled yet
antonio@506
   317
    END LOOP; -- Cur_Invalids
antonio@506
   318
    --  any other to be compiled
antonio@506
   319
    IF count_compiled=0 THEN
antonio@506
   320
      EXIT;
antonio@506
   321
    END IF;
antonio@506
   322
  END LOOP; -- outer loop
antonio@506
   323
  DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   324
  --
antonio@506
   325
  -- Print Message
antonio@506
   326
  IF(LENGTH(v_Message)=1) THEN
antonio@506
   327
    v_Message:='All valid';
antonio@506
   328
    DBMS_OUTPUT.PUT_LINE(v_Message) ;
antonio@506
   329
  ELSIF(LENGTH(v_Message)>80) THEN
antonio@506
   330
    v_Buffer:=v_Message;
antonio@506
   331
    DBMS_OUTPUT.PUT_LINE('>') ;
antonio@506
   332
    WHILE(LENGTH(v_Buffer)>0)
antonio@506
   333
    LOOP
antonio@506
   334
      v_Line:=SUBSTR(v_Buffer, 1, 80) ;
antonio@506
   335
      DBMS_OUTPUT.PUT_LINE(v_Line) ;
antonio@506
   336
      v_Buffer:=SUBSTR(v_Buffer, 81) ;
antonio@506
   337
    END LOOP;
antonio@506
   338
    DBMS_OUTPUT.PUT_LINE('<') ;
antonio@506
   339
    v_Result:=0;
antonio@506
   340
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   341
  ELSE
antonio@506
   342
    DBMS_OUTPUT.PUT_LINE('>' || v_Message || '<') ;
antonio@506
   343
    v_Result:=0;
antonio@506
   344
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   345
  END IF;
antonio@506
   346
  --<<FINISH_PROCESS>>
antonio@506
   347
  IF(p_PInstance_ID IS NOT NULL) THEN
antonio@506
   348
    --  Update AD_PInstance
antonio@506
   349
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
antonio@506
   350
  END IF;
antonio@506
   351
  RETURN;
antonio@506
   352
EXCEPTION
antonio@506
   353
WHEN OTHERS THEN
antonio@506
   354
  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
antonio@506
   355
  IF DBMS_SQL.IS_OPEN(exec_cursor) THEN
antonio@506
   356
    DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   357
  END IF;
antonio@506
   358
  IF Cur_Valids%ISOPEN THEN
antonio@506
   359
    CLOSE Cur_Valids;
antonio@506
   360
  END IF;
antonio@506
   361
END DBA_Recompile;
antonio@506
   362
/-- END
antonio@506
   363
antonio@506
   364
CREATE OR REPLACE PROCEDURE DBA_AFTERIMPORT
antonio@506
   365
AS
antonio@506
   366
/*************************************************************************
antonio@506
   367
  * The contents of this file are subject to the Compiere License
antonio@506
   368
  * Version 2.5.0 ("License"); You may not use this file except in
antonio@506
   369
  * compliance with the License. You may obtain a copy of the License at
antonio@506
   370
  * http://www.compiere.org/license.html
antonio@506
   371
  * Software distributed under the License is distributed on an
antonio@506
   372
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   373
  * implied. See the License for the specific language governing rights
antonio@506
   374
  * and limitations under the License.
antonio@506
   375
  * The Original Code is  Compiere  ERP &  Business Solution
antonio@506
   376
  * The Initial Developer of the Original Code is Jorg Janke  and ComPiere,
antonio@506
   377
  Inc.
antonio@506
   378
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   379
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   380
  * All Rights Reserved.
antonio@506
   381
  * Contributor(s): Openbravo SL
antonio@506
   382
  * Contributions are Copyright (C) 1999-2005 Openbravo, S.L
antonio@506
   383
  *************************************************************************
antonio@506
   384
  * $Id: DBA_AfterImport.sql,v 1.5 2002/10/21 04:49:46 jjanke Exp $
antonio@506
   385
  * $Source: /cvsroot/compiere/db/database/Procedures/DBA_AfterImport.sql,v $
antonio@506
   386
  ***
antonio@506
   387
  * Title:  Run after Import
antonio@506
   388
  * Description:
antonio@506
   389
  * - Recompile
antonio@506
   390
  * - Compute Statistics
antonio@506
   391
  *****************************************************************************/
antonio@506
   392
  -- Statistics
antonio@506
   393
  TYPE RECORD IS REF CURSOR;
antonio@506
   394
    Cur_Stat RECORD;
antonio@506
   395
    --
antonio@506
   396
    v_Cmd VARCHAR2(256):='';
antonio@506
   397
    v_NoC NUMBER:=0;
antonio@506
   398
    --
antonio@506
   399
  BEGIN
antonio@506
   400
    -- Recompile
antonio@506
   401
    DBA_Recompile(NULL) ;
antonio@506
   402
    -- Statistics
antonio@506
   403
    FOR Cur_Stat IN
antonio@506
   404
      (SELECT Table_Name,
antonio@506
   405
        Blocks
antonio@506
   406
      FROM USER_TABLES
antonio@506
   407
      WHERE DURATION IS NULL -- No temporary tables
antonio@506
   408
        AND(LAST_ANALYZED IS NULL
antonio@506
   409
        OR LAST_ANALYZED<SysDate-7)
antonio@506
   410
      )
antonio@506
   411
    LOOP
antonio@506
   412
      v_Cmd:='ANALYZE TABLE ' || Cur_Stat.Table_Name || ' COMPUTE STATISTICS';
antonio@506
   413
      v_NoC:=v_NoC + 1;
antonio@506
   414
      EXECUTE IMMEDIATE v_Cmd;
antonio@506
   415
    END LOOP;
antonio@506
   416
    DBMS_OUTPUT.PUT_LINE('Statistics computed: ' || v_NoC) ;
antonio@506
   417
    --
antonio@506
   418
END DBA_AfterImport;
antonio@506
   419
/-- END
carlos@496
   420
carlos@0
   421
CALL DBA_RECOMPILE(NULL)
carlos@0
   422
/-- END
carlos@0
   423