src-db/database/model/postscript-Oracle.sql
author Juan Pablo Aroztegi <juanpablo.aroztegi@openbravo.com>
Mon, 28 Apr 2008 09:28:08 +0000
changeset 785 8dba91261590
parent 781 d68cf344696f
child 799 fef2c5e2feb7
permissions -rw-r--r--
Fix last update of copyright years in database files
antonio@665
     1
CREATE OR REPLACE function NOW
carlos@497
     2
RETURN DATE
carlos@497
     3
AS
carlos@497
     4
BEGIN
carlos@497
     5
  RETURN SYSDATE;
carlos@497
     6
END NOW;
carlos@497
     7
/-- END NOW
carlos@497
     8
adrian@101
     9
carlos@496
    10
CREATE OR REPLACE FUNCTION ad_script_disable_triggers (p_seqNoStart NUMBER)
carlos@496
    11
  RETURN NUMBER
carlos@496
    12
AS
carlos@496
    13
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    14
 TYPE RECORD IS REF CURSOR;
carlos@496
    15
 Cur_Triggers RECORD;
carlos@496
    16
BEGIN
carlos@496
    17
     FOR Cur_Triggers IN 
carlos@496
    18
      (SELECT OBJECT_NAME AS NAME 
carlos@496
    19
      FROM USER_OBJECTS 
carlos@496
    20
      WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
    21
      ORDER BY OBJECT_NAME 
carlos@496
    22
      ) 
carlos@496
    23
    LOOP 
carlos@496
    24
      v_seqNo:=v_seqNo + 1; 
carlos@496
    25
      INSERT 
carlos@496
    26
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    27
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' DISABLE') ; 
carlos@496
    28
    END LOOP; 
carlos@496
    29
carlos@496
    30
 RETURN v_seqNo;
carlos@496
    31
END ad_script_disable_triggers;
carlos@496
    32
/-- END
carlos@496
    33
carlos@496
    34
CREATE OR REPLACE FUNCTION ad_script_disable_constraints(p_seqNoStart NUMBER)
carlos@496
    35
  RETURN NUMBER
carlos@496
    36
AS
carlos@496
    37
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    38
 TYPE RECORD IS REF CURSOR;
carlos@496
    39
 Cur_Constraints RECORD;
carlos@496
    40
BEGIN
carlos@496
    41
    FOR Cur_Constraints IN 
carlos@496
    42
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
    43
    FROM USER_CONSTRAINTS C1 
carlos@496
    44
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND DELETE_RULE NOT LIKE 'CASCADE' 
carlos@496
    45
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 1 WHEN 'U' THEN 2 WHEN 'P' THEN 3 
carlos@496
    46
      END 
carlos@496
    47
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
    48
    LOOP 
carlos@496
    49
      v_seqNo:=v_seqNo + 1; 
carlos@496
    50
      INSERT 
carlos@496
    51
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    52
        (v_seqNo, 'ALTER TABLE '||Cur_Constraints.TABLE_NAME||' DISABLE CONSTRAINT '||Cur_Constraints.CONSTRAINT_NAME) ; 
carlos@496
    53
    END LOOP; 
carlos@496
    54
carlos@496
    55
 RETURN v_seqNo;
carlos@496
    56
END ad_script_disable_constraints;
carlos@496
    57
/-- END
carlos@496
    58
carlos@496
    59
carlos@496
    60
CREATE OR REPLACE FUNCTION ad_script_enable_triggers(p_seqNoStart NUMBER)
carlos@496
    61
  RETURN NUMBER 
carlos@496
    62
AS
carlos@496
    63
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    64
 TYPE RECORD IS REF CURSOR;
carlos@496
    65
 Cur_Triggers RECORD;
carlos@496
    66
BEGIN
carlos@496
    67
    FOR Cur_Triggers IN 
carlos@496
    68
      (SELECT OBJECT_NAME AS NAME 
carlos@496
    69
    FROM USER_OBJECTS 
carlos@496
    70
    WHERE OBJECT_TYPE='TRIGGER' 
carlos@496
    71
    ORDER BY OBJECT_NAME) 
carlos@496
    72
    LOOP 
carlos@496
    73
      v_seqNo:=v_seqNo + 1; 
carlos@496
    74
      INSERT 
carlos@496
    75
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
    76
        (v_seqNo, 'ALTER TRIGGER '||Cur_Triggers.NAME||' ENABLE') ; 
carlos@496
    77
    END LOOP; 
carlos@496
    78
carlos@496
    79
 RETURN v_seqNo;
carlos@496
    80
END ad_script_enable_triggers;
carlos@496
    81
/-- END
carlos@496
    82
carlos@496
    83
CREATE OR REPLACE FUNCTION ad_script_enable_constraints(p_seqNoStart NUMBER)
carlos@496
    84
  RETURN NUMBER
carlos@496
    85
AS
carlos@496
    86
 v_seqNo      NUMBER := p_seqNoStart;
carlos@496
    87
 TYPE RECORD IS REF CURSOR;
carlos@496
    88
 Cur_Constraints RECORD;
carlos@496
    89
BEGIN
carlos@496
    90
    FOR Cur_ConstraintsEnable IN 
carlos@496
    91
      (SELECT TABLE_NAME, CONSTRAINT_NAME 
carlos@496
    92
    FROM USER_CONSTRAINTS C1 
carlos@496
    93
    WHERE CONSTRAINT_TYPE IN('P', 'U', 'R') AND DELETE_RULE NOT LIKE 'CASCADE' 
carlos@496
    94
    ORDER BY(CASE CONSTRAINT_TYPE WHEN 'R' THEN 3 WHEN 'U' THEN 2 WHEN 'P' THEN 1 
carlos@496
    95
      END 
carlos@496
    96
      ), TABLE_NAME, CONSTRAINT_NAME) 
carlos@496
    97
    LOOP 
carlos@496
    98
      v_seqNo:=v_seqNo + 1; 
carlos@496
    99
      INSERT 
carlos@496
   100
      INTO AD_SCRIPT_SQL VALUES 
carlos@496
   101
        (v_seqNo, 'ALTER TABLE '||Cur_ConstraintsEnable.TABLE_NAME||' ENABLE CONSTRAINT '||Cur_ConstraintsEnable.CONSTRAINT_NAME) ; 
carlos@496
   102
    END LOOP; 
carlos@496
   103
carlos@496
   104
  RETURN v_seqNo;
carlos@496
   105
END ad_script_enable_constraints;
carlos@496
   106
/-- END
carlos@496
   107
carlos@496
   108
carlos@496
   109
CREATE OR REPLACE FUNCTION ad_script_execute (param_Message VARCHAR2)
carlos@496
   110
  RETURN VARCHAR2
carlos@496
   111
AS
carlos@496
   112
 v_Message       VARCHAR2(4000) := '';
carlos@496
   113
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   114
 TYPE RECORD IS REF CURSOR;
carlos@496
   115
 Cur_Script RECORD;
carlos@496
   116
BEGIN
carlos@496
   117
    v_Message := param_Message;
carlos@496
   118
    FOR Cur_Script IN 
carlos@496
   119
      (SELECT STRSQL, SEQNO FROM AD_SCRIPT_SQL ORDER BY SEQNO)
carlos@496
   120
    LOOP 
carlos@496
   121
    BEGIN 
carlos@496
   122
      EXECUTE IMMEDIATE(Cur_Script.STRSQL) ; 
carlos@496
   123
    EXCEPTION 
carlos@496
   124
    WHEN OTHERS THEN 
carlos@496
   125
      
carlos@496
   126
      if (coalesce(length(v_Message),0)!=0) then
carlos@496
   127
        v_Message:=substr(v_Message||'<br><br>',1,2000);
carlos@496
   128
      end if;
carlos@496
   129
      v_Message := substr(v_Message||'@SQLScriptError@ '||Cur_Script.SeqNo||'. @Executing@'||Cur_Script.strSQL||'<br>'||SQLERRM,1,2000);
carlos@496
   130
    END;
carlos@496
   131
  END LOOP;
carlos@496
   132
carlos@496
   133
 IF( LENGTH(v_Message) > 0 ) THEN
carlos@496
   134
    DBMS_OUTPUT.PUT_LINE('Script errors: ' || v_Message);
carlos@496
   135
 END IF;
carlos@496
   136
 return substr(coalesce(v_ResultStr,'') || coalesce(v_Message,''), 1, 2000);
carlos@496
   137
END ad_script_execute;
carlos@496
   138
/-- END
carlos@496
   139
carlos@496
   140
CREATE OR REPLACE FUNCTION ad_script_drop_recreate_index (p_seqNoStart NUMBER)
carlos@496
   141
  RETURN NUMBER
carlos@496
   142
AS
carlos@496
   143
 v_seqNo         NUMBER; 
carlos@496
   144
 v_strSql        VARCHAR2(4000) := '';
carlos@496
   145
 v_strTemp       VARCHAR2(4000) := '';
carlos@496
   146
 v_Message       VARCHAR2(4000) := '';
carlos@496
   147
 v_ResultStr     VARCHAR2(4000) := '';
carlos@496
   148
 TYPE RECORD IS REF CURSOR;
carlos@496
   149
 Cur_UniqueIndex  RECORD;
carlos@496
   150
 Cur_IndexColumns RECORD;
carlos@496
   151
BEGIN
carlos@496
   152
    v_seqNo := p_seqNoStart;
carlos@496
   153
    FOR Cur_UniqueIndex IN (SELECT i.INDEX_NAME, i.TABLE_NAME, i.TABLESPACE_NAME, CONSTRAINT_TYPE
carlos@496
   154
                 FROM USER_INDEXES I left join USER_CONSTRAINTS C1 on c1.INDEX_NAME=I.INDEX_NAME
carlos@496
   155
                 WHERE UNIQUENESS='UNIQUE' AND INDEX_TYPE='NORMAL' AND TABLE_TYPE='TABLE'
carlos@496
   156
               --AND CONSTRAINT_TYPE != 'U'
carlos@496
   157
               ORDER BY INDEX_NAME)
carlos@496
   158
carlos@496
   159
    LOOP
carlos@496
   160
      v_seqNo:=v_seqNo + 1;
carlos@496
   161
      INSERT
carlos@496
   162
      INTO AD_SCRIPT_SQL VALUES (v_seqNo, 'DROP INDEX '||Cur_UniqueIndex.INDEX_NAME) ;
carlos@496
   163
carlos@496
   164
   IF Cur_UniqueIndex.CONSTRAINT_TYPE != 'P' THEN
carlos@496
   165
    v_strSql:='CREATE INDEX '||Cur_UniqueIndex.INDEX_NAME||' ON '||Cur_UniqueIndex.TABLE_NAME||'(';
carlos@496
   166
       v_strTemp:='';
carlos@496
   167
       FOR Cur_IndexColumns IN
carlos@496
   168
         (SELECT COLUMN_NAME
carlos@496
   169
       FROM USER_IND_COLUMNS
carlos@496
   170
       WHERE INDEX_NAME=Cur_UniqueIndex.INDEX_NAME
carlos@496
   171
       ORDER BY COLUMN_POSITION)
carlos@496
   172
       LOOP
carlos@496
   173
         v_strTemp:=v_strTemp ||','|| Cur_IndexColumns.COLUMN_NAME;
carlos@496
   174
       END LOOP;
carlos@496
   175
       v_strSql:=v_strSql || SUBSTR(v_strTemp, 2, 2000) || ') TABLESPACE '||Cur_UniqueIndex.TABLESPACE_NAME;
carlos@496
   176
       INSERT INTO AD_SCRIPT_SQL VALUES(v_seqNo+100000, v_strSql) ;
carlos@496
   177
   END IF;
carlos@496
   178
 END LOOP;
carlos@496
   179
 return v_seqNo;
carlos@496
   180
END ad_script_drop_recreate_index;
carlos@496
   181
/-- END
carlos@496
   182
antonio@506
   183
CREATE OR REPLACE PROCEDURE DBA_RECOMPILE(p_PInstance_ID IN NUMBER) 
antonio@506
   184
AS
antonio@506
   185
/*************************************************************************
juanpablo@771
   186
  * The contents of this file are subject to the Compiere Public
juanpablo@771
   187
  * License 1.1 ("License"); You may not use this file except in
juanpablo@771
   188
  * compliance with the License. You may obtain a copy of the License in
juanpablo@771
   189
  * the legal folder of your Openbravo installation.
juanpablo@771
   190
antonio@506
   191
  * Software distributed under the License is distributed on an
antonio@506
   192
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   193
  * implied. See the License for the specific language governing rights
juanpablo@771
   194
antonio@506
   195
  * and limitations under the License.
antonio@506
   196
  * The Original Code is  Compiere  ERP &  Business Solution
juanpablo@771
   197
  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
juanpablo@771
   198
antonio@506
   199
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   200
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   201
  * All Rights Reserved.
antonio@506
   202
  * Contributor(s): Openbravo SL
juanpablo@771
   203
antonio@506
   204
  * Contributions are Copyright (C) 1999-2005 Openbravo, S.L
juanpablo@771
   205
  *
juanpablo@771
   206
  * Specifically, this derivative work is based upon the following Compiere
juanpablo@771
   207
  * file and version.
antonio@506
   208
  *************************************************************************
antonio@506
   209
  * $Id: DBA_Recompile.sql,v 1.7 2003/03/14 06:11:21 jjanke Exp $
antonio@506
   210
  ***
antonio@506
   211
  * Title:  Recompile all User_Objects
antonio@506
   212
  * Description:
antonio@506
   213
  ************************************************************************/
antonio@506
   214
  -- Logistice
antonio@506
   215
  v_Message VARCHAR2(2000):=' ';
antonio@506
   216
  v_Result NUMBER:=1; --  0=failure
antonio@506
   217
  --
antonio@506
   218
  v_Buffer VARCHAR2(2000):='';
antonio@506
   219
  v_Line VARCHAR(100) ;
antonio@506
   220
  v_PrintInfo CHAR(1):='N'; -- Diagnostic
antonio@506
   221
  --
antonio@506
   222
  CURSOR Cur_Invalids IS
antonio@506
   223
    SELECT object_id,
antonio@506
   224
      object_name,
antonio@506
   225
      object_type
antonio@506
   226
    FROM user_objects
antonio@506
   227
    WHERE status<>'VALID'
antonio@506
   228
      AND object_type IN('VIEW', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA CLASS')
antonio@506
   229
    ORDER BY object_type,
antonio@506
   230
      object_name;
antonio@506
   231
    CURSOR Cur_Valids (p_id NUMBER) IS
antonio@506
   232
      SELECT 'FOUND'  FROM user_objects  WHERE status='VALID'  AND object_id=p_id;
antonio@506
   233
      --  failed compile
antonio@506
   234
    TYPE invalid_tab IS TABLE OF Cur_Invalids%ROWTYPE INDEX BY BINARY_INTEGER;
antonio@506
   235
    invalid_tab_rec invalid_tab;
antonio@506
   236
    count_compiled PLS_INTEGER;
antonio@506
   237
    valid_text VARCHAR2(5) ;
antonio@506
   238
    exec_cursor PLS_INTEGER:=DBMS_SQL.OPEN_CURSOR;
antonio@506
   239
    sql_statement VARCHAR2(200) ;
antonio@506
   240
    count_object PLS_INTEGER:=0;
antonio@506
   241
  BEGIN
antonio@506
   242
    LOOP
antonio@506
   243
      count_compiled:=0;
antonio@506
   244
      FOR ci IN Cur_Invalids
antonio@506
   245
      LOOP
antonio@506
   246
        --  not unsuccessfuly compiled yet
antonio@506
   247
        IF NOT invalid_tab_rec.EXISTS(ci.object_id) THEN
antonio@506
   248
          IF(ci.object_type='JAVA CLASS') THEN
antonio@506
   249
            sql_statement:='ALTER JAVA CLASS "' || ci.object_name || '" RESOLVE';
antonio@506
   250
          ELSIF(ci.object_type='PACKAGE BODY') THEN
antonio@506
   251
            sql_statement:='ALTER PACKAGE ' || ci.object_name || ' COMPILE BODY';
antonio@506
   252
          ELSE
antonio@506
   253
            sql_statement:='ALTER ' || ci.object_type || ' ' || ci.object_name || ' COMPILE';
antonio@506
   254
          END IF;
antonio@506
   255
          --  compile
antonio@506
   256
        BEGIN
antonio@506
   257
          count_object:=count_object + 1;
antonio@506
   258
          DBMS_SQL.PARSE(exec_cursor, sql_statement, DBMS_SQL.NATIVE) ;
antonio@506
   259
        EXCEPTION
antonio@506
   260
        WHEN OTHERS THEN
antonio@506
   261
          NULL;
antonio@506
   262
        END;
antonio@506
   263
        --
antonio@506
   264
        OPEN Cur_Valids(ci.object_ID) ;
antonio@506
   265
        FETCH Cur_Valids INTO valid_text;
antonio@506
   266
        IF Cur_Valids%ROWCOUNT>0 THEN
antonio@506
   267
          IF(v_PrintInfo='Y') THEN
antonio@506
   268
            DBMS_OUTPUT.PUT_LINE('OK: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   269
          END IF;
antonio@506
   270
          count_compiled:=count_compiled + 1;
antonio@506
   271
          CLOSE Cur_Valids;
antonio@506
   272
          EXIT;
antonio@506
   273
        ELSE
antonio@506
   274
          IF(LENGTH(v_Message)<1950) THEN
antonio@506
   275
            v_Message:=v_Message || ci.object_name || ' ';
antonio@506
   276
          END IF;
antonio@506
   277
          IF(v_PrintInfo='Y') THEN
antonio@506
   278
            DBMS_OUTPUT.PUT_LINE('Error: ' || ci.object_type || ' ' || ci.object_name) ;
antonio@506
   279
          END IF;
antonio@506
   280
          --
antonio@506
   281
          invalid_tab_rec(ci.object_id) .object_name:=ci.object_name;
antonio@506
   282
          invalid_tab_rec(ci.object_id) .object_type:=ci.object_type;
antonio@506
   283
          CLOSE Cur_Valids;
antonio@506
   284
        END IF;
antonio@506
   285
      END IF; -- not unsuccessfuly compiled yet
antonio@506
   286
    END LOOP; -- Cur_Invalids
antonio@506
   287
    --  any other to be compiled
antonio@506
   288
    IF count_compiled=0 THEN
antonio@506
   289
      EXIT;
antonio@506
   290
    END IF;
antonio@506
   291
  END LOOP; -- outer loop
antonio@506
   292
  DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   293
  --
antonio@506
   294
  -- Print Message
antonio@506
   295
  IF(LENGTH(v_Message)=1) THEN
antonio@506
   296
    v_Message:='All valid';
antonio@506
   297
    DBMS_OUTPUT.PUT_LINE(v_Message) ;
antonio@506
   298
  ELSIF(LENGTH(v_Message)>80) THEN
antonio@506
   299
    v_Buffer:=v_Message;
antonio@506
   300
    DBMS_OUTPUT.PUT_LINE('>') ;
antonio@506
   301
    WHILE(LENGTH(v_Buffer)>0)
antonio@506
   302
    LOOP
antonio@506
   303
      v_Line:=SUBSTR(v_Buffer, 1, 80) ;
antonio@506
   304
      DBMS_OUTPUT.PUT_LINE(v_Line) ;
antonio@506
   305
      v_Buffer:=SUBSTR(v_Buffer, 81) ;
antonio@506
   306
    END LOOP;
antonio@506
   307
    DBMS_OUTPUT.PUT_LINE('<') ;
antonio@506
   308
    v_Result:=0;
antonio@506
   309
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   310
  ELSE
antonio@506
   311
    DBMS_OUTPUT.PUT_LINE('>' || v_Message || '<') ;
antonio@506
   312
    v_Result:=0;
antonio@506
   313
    DBMS_OUTPUT.PUT_LINE('ERROR') ;
antonio@506
   314
  END IF;
antonio@506
   315
  --<<FINISH_PROCESS>>
antonio@506
   316
  IF(p_PInstance_ID IS NOT NULL) THEN
antonio@506
   317
    --  Update AD_PInstance
antonio@506
   318
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
antonio@506
   319
  END IF;
antonio@506
   320
  RETURN;
antonio@506
   321
EXCEPTION
antonio@506
   322
WHEN OTHERS THEN
antonio@506
   323
  DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
antonio@506
   324
  IF DBMS_SQL.IS_OPEN(exec_cursor) THEN
antonio@506
   325
    DBMS_SQL.CLOSE_CURSOR(exec_cursor) ;
antonio@506
   326
  END IF;
antonio@506
   327
  IF Cur_Valids%ISOPEN THEN
antonio@506
   328
    CLOSE Cur_Valids;
antonio@506
   329
  END IF;
antonio@506
   330
END DBA_Recompile;
antonio@506
   331
/-- END
antonio@506
   332
antonio@506
   333
CREATE OR REPLACE PROCEDURE DBA_AFTERIMPORT
antonio@506
   334
AS
antonio@506
   335
/*************************************************************************
juanpablo@771
   336
  * The contents of this file are subject to the Compiere Public
juanpablo@771
   337
  * License 1.1 ("License"); You may not use this file except in
juanpablo@771
   338
  * compliance with the License. You may obtain a copy of the License in
juanpablo@771
   339
  * the legal folder of your Openbravo installation.
antonio@506
   340
  * Software distributed under the License is distributed on an
antonio@506
   341
  * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
antonio@506
   342
  * implied. See the License for the specific language governing rights
antonio@506
   343
  * and limitations under the License.
antonio@506
   344
  * The Original Code is  Compiere  ERP &  Business Solution
juanpablo@771
   345
  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
antonio@506
   346
  * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke,
antonio@506
   347
  * parts created by ComPiere are Copyright (C) ComPiere, Inc.;
antonio@506
   348
  * All Rights Reserved.
antonio@506
   349
  * Contributor(s): Openbravo SL
juanpablo@785
   350
  * Contributions are Copyright (C) 2001-2008 Openbravo, S.L.
juanpablo@771
   351
  *
juanpablo@771
   352
  * Specifically, this derivative work is based upon the following Compiere
juanpablo@771
   353
  * file and version.
antonio@506
   354
  *************************************************************************
antonio@506
   355
  * $Id: DBA_AfterImport.sql,v 1.5 2002/10/21 04:49:46 jjanke Exp $
antonio@506
   356
  * $Source: /cvsroot/compiere/db/database/Procedures/DBA_AfterImport.sql,v $
antonio@506
   357
  ***
antonio@506
   358
  * Title:  Run after Import
antonio@506
   359
  * Description:
antonio@506
   360
  * - Recompile
antonio@506
   361
  * - Compute Statistics
antonio@506
   362
  *****************************************************************************/
antonio@506
   363
  -- Statistics
antonio@506
   364
  TYPE RECORD IS REF CURSOR;
antonio@506
   365
    Cur_Stat RECORD;
antonio@506
   366
    --
antonio@506
   367
    v_Cmd VARCHAR2(256):='';
antonio@506
   368
    v_NoC NUMBER:=0;
antonio@506
   369
    --
antonio@506
   370
  BEGIN
antonio@506
   371
    -- Recompile
antonio@506
   372
    DBA_Recompile(NULL) ;
antonio@506
   373
    -- Statistics
antonio@506
   374
    FOR Cur_Stat IN
antonio@506
   375
      (SELECT Table_Name,
antonio@506
   376
        Blocks
antonio@506
   377
      FROM USER_TABLES
antonio@506
   378
      WHERE DURATION IS NULL -- No temporary tables
antonio@506
   379
        AND(LAST_ANALYZED IS NULL
antonio@506
   380
        OR LAST_ANALYZED<SysDate-7)
antonio@506
   381
      )
antonio@506
   382
    LOOP
antonio@506
   383
      v_Cmd:='ANALYZE TABLE ' || Cur_Stat.Table_Name || ' COMPUTE STATISTICS';
antonio@506
   384
      v_NoC:=v_NoC + 1;
antonio@506
   385
      EXECUTE IMMEDIATE v_Cmd;
antonio@506
   386
    END LOOP;
antonio@506
   387
    DBMS_OUTPUT.PUT_LINE('Statistics computed: ' || v_NoC) ;
antonio@506
   388
    --
antonio@506
   389
END DBA_AfterImport;
antonio@506
   390
/-- END
carlos@496
   391
carlos@0
   392
CALL DBA_RECOMPILE(NULL)
carlos@0
   393
/-- END
carlos@0
   394