Operating System - HP-UX
1748166 Members
3791 Online
108758 Solutions
New Discussion юеВ

Re: 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 2
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
Kimathi Njeru
Advisor
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