execute immediate giving in ORA-01031: insufficient privileges

[saurabhg29]

hi all,
i have to execute a create tabel statement in pl/sql which i could do it will

execute immediate statement
but when i execute it give
ORA-01031: insufficient privileges

i gave sysdba privileges also but it is still giving it.

procedure secondproc as
a number;
procedure hello as
begin
execute immediate 'create table testvariables(id number,name1 varchar2(12))';
-- execute immediate 'insert into testvariables values (1,''dd'');';
end;
begin
hello;
-- htp.ps('In secproc');
-- execute immediate 'select id into a from testvariables';
-- htp.ps(a);
htp.ps('hello');
end;


please help

[julian]

Run first as SYS:

Grant Create Any Table to User;

It should be given directly for execute immediate, not via a role.

[isaunders]

I have a simmilar problem when creating function based indexes using the execute immediate.

I have granted 'create any index' to the user and it is possible to create conventional indexes using the execute immediate but when I use a function based index I have the 1031 error.

What priveleges need to be granted to create a function based index?

Any clues?

[pdh]

I have similar kind of problem while creating function based indexs. When I use execute immediate it gives "-1031 ORA-01031: insufficient privileges". What privileges does this need. It creates conventional indexes but not function based indexes. Even create index statement gives the same error.

Thanks in advance.

[Umusic]

U need the query rewrite priv to create functional indexes. Grant it directly to a user and it will work!

[pdh]

Thanks so much.