Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Database Link in Stored Procedure
"Woody McKay" <wmckay_at_hydrogenmedia.com> wrote in message news:<%5NR6.9265$651.654975_at_newsread1.prod.itd.earthlink.net>...
> Hello all,
>
> I created a database link "DEVL1" in SQL Plus and it appears to work fine
> and is in user_db_links. But, when I try to use the DB link in a stored
> procedure, I get "PLS-00201: identifier 'OPTIONAL_COVERAGE_TYPES_at_DEVL1' must
> be declared". Can someone give me a tip about using database links in stored
> procedures?
>
> Thanks,
>
> Woody
Well we do this all the time so check the following: Make sure the id used in the link to connect to the remote instance has the necessary privileges in the remote instance
create a local synonym, public or private, for the object
create synonym opt_cover_typ for
owner.optional_coverage_types_at_devl1.domain;
This will allow you to just change the synonym whenever the link or remote object has a change that affects you and all your stored code can pick up the change from the synonym.