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