Re: Database Link in Stored Procedure

I finally figured it out. For some reason it worked in SQL Plus but not in the stored procedure. The stored procedure version began working when I created the link with the full global name.

Thanks,

Woody

"Mark D Powell" <mark.powell_at_eds.com> wrote in message news:178d2795.0106011020.c60583e_at_posting.google.com...
> "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.
>
> -- Mark D Powell --
>
Received on Fri Jun 01 2001 - 13:31:13 CDT