[SOLVED] Need to iterate the result and then execute immediate each result using pl/sql

Need to iterate the result and then execute immediate each result using pl/sql

So I have this query:

select 'alter index '||a.index_owner||'.'||a.index_name|| ' rebuild partition '||a.partition_name 
from dba_ind_partitions a
where a.index_name in ('IDX_PI_T_BSCS_CONTRACT_HISTOR2', 'IDX_PI_T_BSCS_CONTRACT_HISTOR3',  'IDX_PI_T_BSCS_RATEPLAN_HIST_C1')
and a.status =  'UNUSABLE'

And I need to iterate over the result and then execute immediate each statement.

Solution

Basically, you'd convert SELECT you wrote into a cursor FOR loop and execute it.

Here's how; I can't test it, but - if SELECT returns what you want, that's the way to do it.

SQL> show user
USER is "SYS"
SQL> BEGIN
  2     FOR cur_r
  3        IN (SELECT    'alter index '
  4                   || a.index_owner
  5                   || '.'
  6                   || a.index_name
  7                   || ' rebuild partition '
  8                   || a.partition_name
  9                      as l_alter
 10              FROM dba_ind_partitions a
 11             WHERE     a.index_name IN ('IDX_PI_T_BSCS_CONTRACT_HISTOR2',
 12                                        'IDX_PI_T_BSCS_CONTRACT_HISTOR3',
 13                                        'IDX_PI_T_BSCS_RATEPLAN_HIST_C1')
 14                   AND a.status = 'UNUSABLE')
 15     LOOP
 16        EXECUTE IMMEDIATE cur_r.l_alter;
 17     END LOOP;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>