Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dynamic ddl using dbms_sql

Re: dynamic ddl using dbms_sql

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/07
Message-ID: <8f3qcd$a4r$1@nnrp1.deja.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US