Home » SQL & PL/SQL » SQL & PL/SQL » DB_LINK question (Oracle Database 10g Express Edition Release 10.2.0.1.0, UNIX)
DB_LINK question [message #392413] Tue, 17 March 2009 20:50 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi experts,

I'm having some problems in accessing certain fields of a table thru DB LINK in a procedure..

I tried this:


select id from Table1@MyDB_LINK where rownum < 50;

select * from Table2@MyDB_LINK where rownum < 50;



Hooray! It worked. Now for the procedure..

CREATE or replace PROCEDURE MyProc1 (
    v_start        NUMBER,
    v_end        NUMBER
)
IS

d_id                     TABLE1@My_DBLINK.ID%type;                
d_area_id                TABLE2@My_DBLINK.AREA_ID%type;
.
.
.


it returns this error:

PROCEDURE MyShema.MyProc1
On line:  23
PLS-00352: Unable to access another database 'My_DBLINK.ID'



i'm wondering if there's something wrong with my declaration, because it seems i have permission to access those table (NOT in a procedure). Searched more about DB LINK, and i cant specifically find infos regarding my 'declaration' problem.

Hope you will help. Thanks for the time. Good day! Smile

Regards,
Wilbert
Re: DB_LINK question [message #392414 is a reply to message #392413] Tue, 17 March 2009 21:05 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i'm wondering if there's something wrong with my declaration
Oracle concludes a problem exists.

>Hooray! It worked.
What ever "it" may be.

It would be clearer if you used CUT & PASTE to show whole session; exactly what you did & how Oracle responded via sqlplus session.

>it returns this error:
I am unclear what "it" is.
>On line: 23
It would be nice to know what is line 23.

I suggest you place the whole procedure in the remote DB,
so that the code would be accessing local tables.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 17 March 2009 21:05]

Report message to a moderator

Re: DB_LINK question [message #392419 is a reply to message #392413] Tue, 17 March 2009 22:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I believe your are suffering from two problems:

1) you have a syntax error. Try this instead (notice the position of the column names):

d_id        TABLE1@My_DBLINK.ID%type;                
d_area_id   TABLE2@My_DBLINK.AREA_ID%type;

becomes

d_id        TABLE1.ID@My_DBLINK%type;                
d_area_id   TABLE2.AREA_ID@My_DBLINK%type;



2) IN ALL GOOD JEST, you are a little lazy. It took me only 30 seconds to find the answer by doing a google on the error number. Here in fact is the page I referenced.

http://forums.oracle.com/forums/thread.jspa?threadID=688986

Do not feel badly. Every good programmer at heart is a lazy bumm, including myself. It is one of our attributes that makes us good at our job. The trick is to exploit the lazy gene we were born with, into helping us create things so that we work smart and not hard. Google is one of those inventions that our fellow developers has created for us for this specific purpose. I use it heavily because I am super lazy.

Good luck, Kevin
Re: DB_LINK question [message #392422 is a reply to message #392413] Tue, 17 March 2009 23:27 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
@BlackSwan

Thanks for the suggestion..

@ Kevin Meade

Thanks and sorry about that, i guess I kept on searching the wrong search strings.. Again big thanks Kevin Meade! Smile

[Updated on: Tue, 17 March 2009 23:28]

Report message to a moderator

Re: DB_LINK question [message #392573 is a reply to message #392419] Wed, 18 March 2009 08:19 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Kevin Meade wrote on Tue, 17 March 2009 23:42


Do not feel badly. Every good programmer at heart is a lazy bumm, including myself. It is one of our attributes that makes us good at our job. The trick is to exploit the lazy gene we were born with, into helping us create things so that we work smart and not hard.


If I could only come up with an emoticon to properly show the reaction to this paragraph. Hahahahahaha.
Previous Topic: Error in Inserting and Updating Data on LONG Datatype Column
Next Topic: Connect By Query
Goto Forum:
  


Current Time: Fri Dec 02 20:44:59 CST 2016

Total time taken to generate the page: 0.11764 seconds