SQL & PL/SQL

Hello,

I'm using Oracle 11g.

I have a procedure in a package that does an EXECUTE IMMEDIATE to rebuild an index that is failing with an
ORA-01031: insufficient privileges error.

Here's the procedure code:

PROCEDURE rebuild_index (ind_name IN VARCHAR2) IS

stmt varchar2(300):=NULL;
err_msg varchar2(500) :=NULL;

BEGIN
dbms_output.put_line('******* Rebuild Index '||ind_name||' *******');
-- dbms_output.put_line(ind_name);
stmt:='Alter Index blegc.'||ind_name||' Rebuild Online Logging';
-- dbms_output.put_line(stmt);

EXECUTE immediate ' ' || stmt;
exception
when others then
err_msg := sqlerrm;
dbms_output.put_line('ERROR : '||err_msg);
RAISE;
END rebuild_index;



Here's the call to the procedure:

exec dbms_bflow.rebuild_index('b_6111_03');

ORA-01031: insufficient privileges


Here's the string of code that is used by the EXECUTE IMMEDIATE:

Alter Index blegc.b_6111_03 Rebuild Online Logging

I know that the procedure itself executes as I have printed messages from it. It bombs on the
EXECUTE IMMEDIATE in the procedure.

Now here's the strange part ... in another database this code runs fine. When I migrated this code
to a second database it doesn't work. Not too surprising you'd think, 'Just a simple privilege problem.'
This is very strange because the user owns the package. He owns the tables and indexes and has no
problem with running the "Alter Index blegc.b_6111_03 Rebuild Online Logging" statement all
by itself.

It is my understanding from Oracle that the criteria for having the ability to rebuild indexes is that you are either
building an index in your own schema and/or you have the ALTER ANY INDEX system privilege granted
to you.

In this case the user is the owner of the index and table. That would seemed get around any requirement
for direct privilege grants on the affected objects as opposed to granted roles that are inoperative since
this is a procedure.

I'd really appreciate your thoughts on this.

Thanks,

Richard