Database Links in Stored Procedure

[Woody McKay]

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@DEVL1' must
be declared". Can someone give me a tip about using database links in stored
procedures?

Thanks,

Woody

[Lazar Ivancevic]

Woody McKay wrote:

For the user of the remote database (dblink DEVL1) the object
OPTIONAL_COVERAGE_TYPE is unknown.
Create a (user/public) synonym for this object in the remote database

Lazar Ivancevic

[Woody McKay]

I did this in sql plus and was able to use the synonym in sql plus but it
still causes the same error in a stored procedure.
Any ideas?

Woody

"Lazar Ivancevic" <l...@propack-data.com> wrote in message
news:3B17B2E6...@propack-data.com...

[Sybrand Bakker]

"Woody McKay" <wmc...@hydrogenmedia.com> wrote in message
news:unPR6.9566$651.6...@newsread1.prod.itd.earthlink.net...

Just must have access to the object thru a role. Roles are not observed
during compilation of stored procedures.
You need either a direct grant or define the procedure with authid invoker.
So this has *nothing* to do with your database link.

Hth,

Sybrand Bakker, Oracle DBA

[Thomas Kyte]

A copy of this was sent to "Woody McKay" <wmc...@hydrogenmedia.com>
(if that email address didn't require changing)

can we see a cut and paste of a sqlplus session that looks something like this:

scott@DEV816> create database link ora8i.world using 'ora8i.us.oracle.com';
Database link created.

scott@DEV816> select count(*) from t...@ora8i.world;

COUNT(*)
----------
0

scott@DEV816> begin
2 for x in ( select * from t...@ora8i.world ) loop
3 null;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

scott@DEV816> create or replace procedure p
2 as
3 begin
4 for x in (select * from t...@ora8i.world ) loop
5 null;
6 end loop;
7 end;
8 /

Procedure created.

scott@DEV816> exec p

PL/SQL procedure successfully completed.

--
Thomas Kyte (tk...@us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp

[Woody McKay]

Thanks Tom,

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. Does this sound right?

TIA,

Woody

"Thomas Kyte" <tk...@us.oracle.com> wrote in message
news:glifht4bggm3kri4e...@4ax.com...