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

"Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message news:vvnkcvkho4vj5ntqrm193l9mh4pbjf78fl_at_4ax.com...
> 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

Never mind that it is usually not a smart thing to do. This smells like a sql server conversion problem. Don't create tables in stored procs just to temporarily store data. You don't need to and it will cause your performance and scalability to suck. If you really really must have a temp table look in the manual for global temporary tables. Jim Received on Tue May 20 2003 - 21:37:40 CDT