Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Creating synonym inside a procedure

SOLVED
Go to solution
Parthiban Dhananjeyan
Occasional Contributor

Creating synonym inside a procedure

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
2 REPLIES
Brian Crabtree
Honored Contributor

Re: Creating synonym inside a procedure

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
Solution

Re: Creating synonym inside a procedure

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