Creating synonym inside a procedure

[Parthiban Dhananjeyan]

I tried to create a private synonym(on another schema) with a procedure but it throws a"insufficient previliges" error. But when I execute the cmd in SQL prompt it works. Here is my procedure..

create or replace procedure grant_read
as
cursor T1cursor IS
select tname
from tab;
tname tab.tname%type;

begin

open T1cursor;

loop
FETCH T1cursor into tname;

exit when T1cursor%NOTFOUND;

execute immediate 'create synonym gisreader.' || tname || ' for ' || tname;
/* execute immediate 'grant select on ' || tname || ' to gisreader'; */

end loop;

close T1cursor;

end;

Thanx much for the help!!!!!!


Parthiban

[Brian Crabtree]

As the system or sys user, grant the "create synonym" privilege directly to the user. The reason for this is that procedures cannot inherit privileges from roles. It should work normally after that.

Brian

[Kimathi Njeru · Accepted solution]

I may be mistaken but as far as i know, you cannot execute DDL statements inside a stored procedure without the use of the oracle supplied utility DBMS_SQL or the statement EXECUTE IMMEDIATE.

It is worth a check at http://metalink.oracle.com/
Hope this helps.
Kim