Create or Replace Synonym Strange Problem
476841Apr 15 2006 — edited Apr 17 2006I 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