Create table problem using Dynamic Query

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

Code:

---

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:

Code:

---

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

hi

i faced same prob earlier on 9i. user could create table normally but couldn't thru a exec immediate in a procedure.

it was kind of funny because i simply had to login as system and give the create table permission to user again explicitly.. then it worked.

hope it works in ur case too.

Thanks for your reply,
I think one should grant "create table" and "execute procedure" directly not through a role to that user.
This way it can do it perfectly.
-Regards