Hi Friends,

I created a procedure as follows

SQL> create procedure proctab (table_name in varchar2,column1 in varchar2,column2 in varchar2)
2 as
3 begin
4 execute immediate 'create table ' || table_name || ' (' || column1 || ' varchar2(10),'
5 || column2 || ' varchar2(10))';
6 end;
7 /

Procedure created.

when i am trying to execute the above procedure its giving the following error


SQL> execute proctab('test10','name','city');
BEGIN proctab('test10','name','city'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.PROCTAB", line 4
ORA-06512: at line 1


i connected as system/manager
i think "system" has all(create procedure , execute any procedure) the privileges. but the above statement giving the error that " insufficient privileges .

is there any special privileges to be granted when we are using the dynamic sql ? Because i have used dynamic sql in the procedure.

please tell me what are the additional privileges that i have to grant to system? and how to grant the privileges to "system" . because to grant the privileges first we have to connect to the database know, so by what user shall i connect to the datbase to grant the privileges to "system"(system has the dba role) .



please help me


regards

SrinivasM



[Edited by srinivasm on 01-09-2001 at 10:25 PM]