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: 2108
Registered: October 2003
Location: Hyderabad, India
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: Delete on child-table locks father-table
Next Topic: Using UTL file Read selected text from text file
Goto Forum:

Current Time: Thu Aug 24 05:48:50 CDT 2017

Total time taken to generate the page: 0.11596 seconds