Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dynamic ddl using dbms_sql
In article <39147F14.BB18831D_at_Unforgettable.com>,
Kenneth C Stahl <BlueSax_at_Unforgettable.com> wrote:
> 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.
>
you've got the ability to rebuild the indexes via a privelege granted to a role.
see http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html for the reason, how to test for it and the solution.
-- Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries http://osi.oracle.com/~tkyte/index.html -- Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Sun May 07 2000 - 00:00:00 CDT