Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql problem
dynamic sql problem [message #207016] Sun, 03 December 2006 22:17 Go to next message
Messages: 111
Registered: November 2006
Senior Member
i am using dynamic sql to create a table

create or replace procedure test_dynamicSQL2
	theSQL varchar2(100);

	theSQL:='CREATE TABLE tbl_test(C1 NUMBER(4),C2 VARCHAR2(10))';
	execute immediate theSQL;

i am getting this error

SQL> execute test_dynamicsql2;
BEGIN test_dynamicsql2; END;

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TEST_DYNAMICSQL2", line 7
ORA-06512: at line 1

i am able to use truncate statement using dynamic sql without any problem,
then why this error when using create statement?
Re: dynamic sql problem [message #207049 is a reply to message #207016] Mon, 04 December 2006 00:39 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Cross check by creating the table using SQL.
If you get the same error, you need to give CREATE TABLE privilege to that schema.

Previous Topic: to get values between date
Next Topic: outer joins
Goto Forum:

Current Time: Sat Apr 29 20:54:02 CDT 2017

Total time taken to generate the page: 0.06123 seconds