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