Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Create or Replace Synonym Strange Problem

476841Apr 15 2006 — edited Apr 17 2006
I need to have an ability for database users to update private synonyms from a procedure. Can anyone please provide some insight on the following code.

This syntax works perfectly fine
begin
execute immediate 'CREATE or replace SYNONYM USER_A.TBL_A FOR MAIN_DB.TBL_B';
end;


But when I do this
CREATE OR REPLACE PROCEDURE update_syn IS
BEGIN
execute immediate 'CREATE or replace SYNONYM USER_A.TBL_A FOR MAIN_DB.TBL_B';
END update_syn;
/

begin
update_syn;
end;

I get a ORA-01031: insufficient privileges error
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 15 2006
Added on Apr 15 2006
3 comments
2,203 views