DYNAMIC CREATION OF SYNONYM FROM A REMOTE DATABASE
559589Jun 5 2007 — edited Jun 5 2007Hi Gurus,
This is Oracle version I am using:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
Let us say I am local DB user AAA and I need to dynamically create a synonym (BBB.synonym) of a sequence (e.g. BBB.sequence - where BBB is another user/schema and the owner of all the schemas) from remote DB.
What do I need to be able to accomplish this?
I tried using the code below in a stored procedure but I am getting insufficient privileges error whenever I execute the stored procedure as AAA. What am I missing? And I need the public synonym to be owned by BBB schema.
sqlstring := 'CREATE PUBLIC SYNONYM ' || in_seqname || ' FOR BBB.' || in_seqname || '@' || in_prim_dbname;
DBMS_SQL.PARSE(cursor_name, sqlstring, DBMS_SQL.V7);
rows_processed := DBMS_SQL.execute(cursor_name);
where:
in_prim_dbname is the remote database
in_seqname is the dynamic synonymn name for the sequence from remote DB
Thanks in advance for your help.
Alex