Rebuild index through loop in PLSQL

Question

FOR r IN (select 'DRR_DEV.' ||object_name as obj from dba_objects where object_type = 'INDEX' 
and owner ='DRR_DEV')
  LOOP
        l_sql := 'ALTER INDEX '||r.obj||' REBUILD'||'';
        execute immediate l_sql;
  END LOOP; 

Above FOR..IN loop, loop all the indexes of the particular schema and rebuild. I am getting few error at the beginning of the loop and the line of ALTER statement.

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00942: table or view does not exist
PL/SQL: Statement ignored
PLS-00364: loop index variable 'R' use is invalid

I able to manually run the select statement but not through PLSQL block, what might go wrong?

Answer 1

It seems that you don't have a privilege to access DBA_OBJECTS. Either connect as SYS and run such a code, or grant required privileges to some other user, or - even better - connect as DDR_DEV.

Ah, outside of PL/SQL it works - sorry, didn't notice that earlier. The fact is: privileges acquired via roles won't work in PL/SQL. So, if that's the case, grant SELECT ON DBA_OBJECTS directly to user that runs your PL/SQL code.

I removed condition which restricts OWNER as there's no such column in USER_OBJECTS.

Here's an example ran on my SCOTT user:

SQL> CREATE TABLE test (id NUMBER);

Table created.

SQL> CREATE INDEX i1
  2     ON test (id);

Index created.

SQL> DECLARE
  2     l_sql   VARCHAR2 (100);
  3  BEGIN
  4     FOR r IN (SELECT object_name AS obj
  5                 FROM user_objects
  6                WHERE object_type = 'INDEX'             -- AND owner = 'DRR_DEV'
  7                                           )
  8     LOOP
  9        l_sql := 'ALTER INDEX ' || r.obj || ' REBUILD' || '';
 10        DBMS_OUTPUT.put_line (l_sql);
 11
 12        EXECUTE IMMEDIATE l_sql;
 13     END LOOP;
 14  END;
 15  /
ALTER INDEX I1 REBUILD

PL/SQL procedure successfully completed.

SQL>