dynamic ddl using dbms_sql

I have a problem that seems odd to me. Rdbms is Oracle 8.1.5 and OS is SunOS 5.6.

I have created some pl/sql in which I issue an ALTER INDEX...REBUILD for several indexes.

If I simply run it as the owner of the indexes from a PL/SQL script from within sqlplus the routine runs find and all of my indexes are rebuilt. No problems at all.

However, if I make the simple change of removing the 'Declare' from the script and replacing it with a 'create procedure rebuildindexes as' and then compile it as a stored procedure, the compile reports no errors but when I try to execute the code I receive a -1031 error.

The owner of the stored procedure is the same as the owner of the indexes and at all times I am only using that account. The literature provides some warnings about 1031 errors when the stored procedure is owned by a different userid then the objects which is manipulating, but that simply doesn't apply here because I'm using a single account for everything.

What gives? I don't mind executing the code as a stand-alone plsql script, but my eventual intent is to use pl/sql wrapper so I can hide my source code and to do that I must be able to use a stored procedure. Received on Sat May 06 2000 - 00:00:00 CDT