Re: execute DDL from an stored procecure, using native dynamic sql (ORA v8.1.6)

On Tue, 20 May 2003 17:39:12 +0200, "Jon" <aaa_at_bbb.ccc> wrote:

>
>Hi,c onnected as a user with DBA role, I am trying to execute a procedure
>with some
>native dynamic sql statements embebed that tries to do some DDL (...create
>table), however,
>when trying to run the stored procedure, and altough the user I am connected
>with has DBA role,
>it returns the following error: ORA-01031: insufficient privileges. Do I
>need any other
>special privilege for running native dynamic sql and DDL instructions? ...
>thanks
>

This question has been asked over and over and over again. Roles are being ignored during compilation of a stored procedures. You have privilege through a role, hence you don't have privilige in a stored procedure.
You need (in order of preference)
create procedure ... authid current_user (as this is 8i)
or grant create table to the owner of the procedure directly (not preferred, as grants by SYS are not exported)

PLEASE ALWAYS search google before posting a question of which you can assume it is a FAQ

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue May 20 2003 - 12:06:55 CDT