PACKAGE BODY: APPS.MSC_PS_SNO_TAB_ANLYZ

Source

1 PACKAGE BODY MSC_PS_SNO_TAB_ANLYZ AS
2 /* $Header: MSCANLYB.pls 120.0.12010000.3 2010/01/06 06:45:56 saskrish noship $ */
3 PROCEDURE ANALYZE_PS_SNO_TABLES (a in number) is
4 
5     TYPE         CUR_TYPE IS REF CURSOR;
6     AnalyzeCur   CUR_TYPE;
7 
8     LV_TAB       VARCHAR2(50);
9     T_NAME       VARCHAR2(50);
10     V_MODEL      VARCHAR2(20);
11     V_QUERY_STR1 VARCHAR2(500);
12 
13 BEGIN
14     V_MODEL      := 'REFERENCE';
15     V_QUERY_STR1 := 'SELECT DISTINCT A.TABLE_NAME TABLE_NAME ' ||
16 		    'FROM DBA_TABLES A, MSC_INT_MODEL_NAVIGATION B '||
17 		    'WHERE A.TABLE_NAME   = TRIM(UPPER(B.TABLE_NAME)) '||
18 		      'AND A.OWNER        = MSC_UTIL.GET_SCHEMA_NAME(724) '||
19                       'AND UPPER(B.MODEL) = :V_MODEL ';
20 
21     SELECT TRIM(UPPER(TABLE_NAME)) INTO T_NAME
22       FROM DBA_TABLES
23      WHERE TABLE_NAME = 'MSC_INT_MODEL_NAVIGATION'
24        AND OWNER      = MSC_UTIL.GET_SCHEMA_NAME(724);
25 
26     IF T_NAME = 'MSC_INT_MODEL_NAVIGATION'
27     THEN
28         OPEN AnalyzeCur FOR V_QUERY_STR1 USING V_MODEL;
29         LOOP
30             FETCH AnalyzeCur INTO LV_TAB;
31             EXIT WHEN AnalyzeCur%NOTFOUND;
32             EXECUTE IMMEDIATE 'ANALYZE TABLE '||MSC_UTIL.GET_SCHEMA_NAME(724)||'.'||LV_TAB||' DELETE STATISTICS';
33         END LOOP;
34         COMMIT;
35     END IF;
36 
37 EXCEPTION
38 WHEN OTHERS THEN
39     RETURN;
40 END ANALYZE_PS_SNO_TABLES;
41 
42 END MSC_PS_SNO_TAB_ANLYZ;