Re: dynamic ddl using dbms_sql

Hi,
  ensure that the correct privilege is granted directly and not through a role.

hth,
cindy

Kenneth C Stahl 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.
Received on Sat May 06 2000 - 00:00:00 CDT