Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: [PL/SQL] Dynamic SQL: execute immediate with insufficient privileges
Manfred Pruntsch wrote:
> Hi all,
>
> I try to get a create table statement running in a stored procedure.
> The code looks:
>
> begin
> ...
> EXECUTE IMMEDIATE 'drop table temp_tbl';
> EXECUTE IMMEDIATE 'create table temp_tbl(id integer, entry varchar2(64))';
> ....
> end;
>
> I've got the error message 'ORA-01031: insufficient privileges' while
> performing the second execute. The first one works fine. When I'm running
> the create statement separately (e.g. sql worksheet) it works.
> I'm always logged on as the same user.
> Any hints would be appriciated.
>
> regards
> Manfred
My 'hint' would be to stop trying to be a SQL Server developer in Oracle.
There is little to no excuse for creating tables in a stored procedure. If you
need a temp
table, and I emphasize "need" rather than want because they are almost never
needed
or desirable ... use global temporary tables (find information at
http://tahiti.oracle.com).
What you are trying to do kills scalability and performance, and shows a
disregard for
Oracle architecture and best practices.
Daniel Morgan Received on Thu Feb 27 2003 - 10:38:30 CST