Re: ora-01031 (insuff priv) while create table in package

"Norbert Winkler" <norbert.winkler1_at_gmx.de> wrote in message news:1m1vnzjxjn4k$.18fffz57ac0tj$.dlg_at_40tude.net...
> Hi,
>
> I can't do a create table in a package procedure. A drop jedoch is
> possible.
> With google I doesn't find any hint for a necessary privilege.
>
> Here is the test package:
> _____________________________________
> CREATE OR REPLACE package DDL_IN_PACKAGE is
> procedure TestDROP;
> procedure TestCreate;
> end DDL_In_Package;
> /
> CREATE OR REPLACE package body DDL_IN_PACKAGE is
> procedure TestDROP is
> begin
> execute immediate 'DROP TABLE XXX';
> end;
> procedure TestCreate is
> begin
> execute immediate 'CREATE TABLE XXX (a integer)';
> end;
> end DDL_In_Package;
> /
> _____________________________________
>
>
> Here is the code that works:
>
> _____________________________________
> -- drop TABLE XXX;
> CREATE TABLE XXX (a integer);
>
> begin
> ddl_in_package.testdrop;
> -- ddl_in_package.testcreate;
> end;
> /
>
> Here is the code that fails:
> _____________________________________
> drop TABLE XXX;
> --CREATE TABLE XXX (a integer);
>
> begin
> -- ddl_in_package.testdrop;
> ddl_in_package.testcreate;
> end;
> /
> -->
> ORA-01031: insufficient privileges
> ORA-06512: at "DDL_IN_PACKAGE", line 10
> ______________________________________
>
> Thanks for any suggestion
>
> --
> Norbert

Why do you want to do this? In general this is a very bad idea. If you want a "temporary table" in a manner similar to MS SQLServer uses in stored procs then look up global temporary table in the Oracle Docs. A global temporary table is created 1 time not by each user. Jim Received on Mon Jul 14 2008 - 08:49:09 CDT