 | |
ora-01031 (insuff priv) while create table in package
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
ora-01031 (insuff priv) while create table in package -
07-14-2008
, 08:25 AM
#2
| |||
| |||
|
|
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 |
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
#3
| |||
| |||
|
|
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 |
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
#4
| |||
| |||
|
|
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 |
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
#5
| |||
| |||
|
|
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 |
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
#6
| |||
| |||
|
|
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 |
Privs acquired via ROLE do not apply within PL/SQL named procedures.
#7
| |||
| |||
|
|
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 |
Privs acquired via ROLE do not apply within PL/SQL named procedures.
#8
| |||
| |||
|
|
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 |
Privs acquired via ROLE do not apply within PL/SQL named procedures.
#9
| |||
| |||
|
|
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 |
Privs acquired via ROLE do not apply within PL/SQL named procedures.
#10
| |||
| |||
|
|
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. |
you.
Quote:
|
Jim |
I've written a package that manage nested sets.
It uses two tables:
one for the attributes and the other for the tree structures.
I've written it with dynamical SELECT/INSERT/UPDATES/DELETE statements, so
that the user can choose the table names and the names of essential fields
of the attribut-table.
(there is i.E a SetTabNameTree procedure)
So he can add nested-functionality to his existing flat tables without
changing anything.
Now I want to write a service-package that create all tables, sequences
triggers and views withe the user choosen names.
If it doesn't work, I've to write a client-application to create the
necessary database-objects, or the user must do it by a string-replaces
itself.
--
Norbert
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.