OraFAQ Forum: Server Administration » Create table problem using Dynamic Query

Home » RDBMS Server » Server Administration » Create table problem using Dynamic Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create table problem using Dynamic Query [message #135986]

Mon, 05 September 2005 04:21

Achchan
Messages: 86
Registered: June 2005

Member

Hi all,
I want to create a temporary table within a stored procedure so I decided to do it using a dynamic query:

create or replace procedure p1
as
 begin
 execute immediate 'CREATE GLOBAL TEMPORARY TABLE tt(id number(2))';
 end;
/

It created successfuly but when I execute that procedure I got:

SQL> exec p1;
BEGIN p1; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.P1", line 4
ORA-06512: at line 1

While I can create that table using the same user without any problem!
My question is:What privilege should I grant to user(minimum of privileges please! ) to execute that procedure successfuly?
-Thanks

Report message to a moderator

Re: Create table problem using Dynamic Query [message #135998 is a reply to message #135986]

Mon, 05 September 2005 05:45

somnath74
Messages: 6
Registered: June 2005

Junior Member

Hi,
In case you are executing the stored procedure with some other schema other than the schema under which the STP has been compiled then you need to grant the EXECUTE permissioon on the STP to that schema which is executing the STP.

Also please note that the privileges that are applicable when a STP is executed is all that pertains to the owner of the STP and not that of the invoker.

Hope this helps !!

Regards,
Somnath

Report message to a moderator

Re: Create table problem using Dynamic Query [message #136027 is a reply to message #135986]

Mon, 05 September 2005 08:21

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Even to execute in the own schema, you need an explicity create table privilege.
Privelege granted via roles are not seen by pl/sql.

Report message to a moderator

Re: Create table problem using Dynamic Query [message #136163 is a reply to message #135986]

Tue, 06 September 2005 08:14

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Note that in general, when people attempt to do this, it is because they are familiar with another database besides oracle and are trying to copy what they did in that database. Normally, creating a temporary table as you are trying to do is something that is not needed in Oracle, and there are better ways to accomplish your goal.

Report message to a moderator

Re: Create table problem using Dynamic Query [message #136182 is a reply to message #135986]

Tue, 06 September 2005 09:39

Achchan
Messages: 86
Registered: June 2005

Member

Thanks that was so useful for me.

Report message to a moderator

Re: Create table problem using Dynamic Query [message #136317 is a reply to message #135986]

Wed, 07 September 2005 08:51

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

create or replace procedure p1 AUTHID CURRENT_USER
as
begin
execute immediate 'CREATE GLOBAL TEMPORARY TABLE tt(id number(2))';
end;
/

It will work, I think.

http://www.anysql.net/en/

Report message to a moderator

Previous Topic:	Patching Oracle 8i Enterprise Edition from 8.1.7.0 to 8.1.7.4
Next Topic:	What is an Undo Tablespace?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 10:18:27 CST 2025