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

Home -> Community -> Usenet -> c.d.o.misc -> Re: [PL/SQL] Dynamic SQL: execute immediate with insufficient privileges

Re: [PL/SQL] Dynamic SQL: execute immediate with insufficient privileges

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Thu, 27 Feb 2003 08:38:30 -0800
Message-ID: <3E5E3F06.A8A48D27@exesolutions.com>


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

Original text of this message

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