It is not getting in the loop....tho values exist...


1 CREATE OR REPLACE PROCEDURE analyze_partition(
2 part_name IN VARCHAR2)
3 IS
4 TYPE x_type IS REF CURSOR;
5 x x_type;
6 x_part_name varchar2(200) := upper(part_name);
7 BEGIN
8 dbms_output.put_line('X_PART_NAME: '|| x_part_name);
9 for x in (select table_name,partition_name from dba_tab_partitions
10 where table_owner = 'MDM3'
11 and partition_name like 'x_part_name%'
12 and last_analyzed is null)
13 loop
14 begin
15 dbms_output.put_line('TABLE : ' || x.table_name);
16 dbms_output.put_line('PART_NAME : ' || x.partition_name);
17 dbms_output.put_line('TESTING ');
18 /*
19 sys.dbms_stats.gather_table_stats(ownname => 'MDM3', -
20 tabname => x.table_name, -
21 partname => x.partition_name, -
22 estimate_percent => 30, -
23 block_sample => false,-
24 method_opt => 'FOR ALL COLUMNS SIZE 1', -
25 degree => 8, -
26 granularity => 'default', -
27 cascade => true , -
28 stattab => null, -
29 statid => null, -
30 statown => null);
31 */
32 exception
33 when others then
34 dbms_output.put_line('### ERROR: ' || sqlerrm);
35 end;
36 dbms_output.put_line('TESTING ');
37 end loop;
38* end;
SQL> /

Procedure created.

SQL>
SQL>
SQL>
SQL> exec analyze_partition('MTH2005JAN');
X_PART_NAME: MTH2005JAN

PL/SQL procedure successfully completed.

SQL>

1 select table_name,partition_name from dba_tab_partitions
2 where table_owner = 'MDM3'
3 and partition_name like 'MTH2005JAN%'
4* and last_analyzed is null
SQL> /

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_ENTITY_DIM MTH2005JAN_DIM_ENTITY
T_MODEL_DIM MTH2005JAN_DIM_MODEL
T_MILN_XTRCT MTH2005JAN_FACT_MILN
T_MLS_XTRCT MTH2005JAN_FACT_MLS
T_MMBA_XTRCT MTH2005JAN_FACT_MMBA
T_MMER_XTRCT MTH2005JAN_FACT_MMER
T_MMTG_XTRCT MTH2005JAN_FACT_MMTG
T_MPCB_XTRCT MTH2005JAN_FACT_MPCB

8 rows selected.

SQL>
SQL>