Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate error
execute immediate error [message #209501] Fri, 15 December 2006 04:06 Go to next message
user82
Messages: 49
Registered: June 2006
Location: Romania
Member
I need to create a temporary table from a procedure, but i get insufficient privileges error...
The procedure looks like this:

declare
STR VARCHAR2(100);
begin
STR := 'CREATE GLOBAL TEMPORARY TABLE TEMP( T VARCHAR2(1))ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE (STR);
end exec_ddl;

then i try to execute the proc.

SQL> execute exec_ddl;

begin
exec_ddl;
end;

ORA-01031: insufficient privileges
ORA-06512: at "MARIAN.EXEC_DDL", line 5
ORA-06512: at line 2


the owner of the procedure has privileges to create such object. Can anyone suggest me how to do this ???

Thanks !!!
Re: execute immediate error [message #209503 is a reply to message #209501] Fri, 15 December 2006 04:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You (normally) should not create tables at runtime. Create them only once.

To be able to do what you tried, you need to have create table privileges assigned directly to you, not through a role.
Previous Topic: URGENT - display count for criteria....
Next Topic: how query is working
Goto Forum:
  


Current Time: Fri Dec 09 02:15:19 CST 2016

Total time taken to generate the page: 0.12605 seconds