Home » SQL & PL/SQL » SQL & PL/SQL » why I can not "create a table " in procedure? (oracle 10.2.0 , RHEL5)
why I can not "create a table " in procedure? [message #307237] Tue, 18 March 2008 04:05 Go to next message
crazyzhou
Messages: 38
Registered: March 2008
Location: china
Member
why I the privilege is not sufficient?
create or replace procedure test_cr AS
BEGIN
  EXECUTE IMMEDIATE 
  'CREATE TABLE test (a NUMBER)';
END;  

SQL> exec test_cr;

begin test_cr; end;

ORA-01031: insufficient privileges
ORA-06512: at "PRIMAL.TEST_CR", line 3
ORA-06512: at line 1


actually, I can create a table successfully in a block:
declare
begin
execute immediate '
CREATE TABLE test (a NUMBER)
';
end;


thanks for your help.

[Updated on: Tue, 18 March 2008 04:20]

Report message to a moderator

Re: why I can not "create a table " in procedure? [message #307240 is a reply to message #307237] Tue, 18 March 2008 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Roles are not enabled in a procedure.

Regards
Michel
Re: why I can not "create a table " in procedure? [message #307271 is a reply to message #307240] Tue, 18 March 2008 05:21 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Read more about it here. And before anyone asks: why would anyone want to create tables on the fly? This is Oracle, not SQL Server.

MHE
Previous Topic: Converting rows into column
Next Topic: Find rowtype / record type dynamically
Goto Forum:
  


Current Time: Fri Dec 02 14:22:01 CST 2016

Total time taken to generate the page: 0.10978 seconds