Home » SQL & PL/SQL » SQL & PL/SQL » issue with db links .. (Oracle, 9i client software on windows.)
issue with db links .. [message #334687] Thu, 17 July 2008 12:42 Go to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

hello,
Can you help me here ..

Issue :
I have a db A which is 10.2.0.3, database B which is 10.2.0.3
there is a db link from A -> B and when i say select count(*) from dual@testlink from server it works fine ..from my client if i say the same, I am getting the error ORA-06502

07/17/2008 System@ Dmstest> select count(*) from dual@testlink;
select count(*) from dual@testlink
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11
ORA-02063: preceding 3 lines from TESTLINK

I tried doing a test by creating a db link from B -> C { 10.2.0.3 } and db link works fine from both Client and server ..
what could be issue with Database A ?

any thoughts please..

-Sai
Re: issue with db links .. [message #334688 is a reply to message #334687] Thu, 17 July 2008 12:55 Go to previous messageGo to next message
evoradba
Messages: 104
Registered: April 2005
Location: Canada
Senior Member
try this first to see which links do you have configured on your client

select db_link, username from all_db_links;


then you can do your select assuming you have some links configured

Re: issue with db links .. [message #334689 is a reply to message #334687] Thu, 17 July 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use DUAL for your test.
DUAL is special and its behaviour changes between 9.2 and 10.2.
Make your test with a real table.

Regards
Michel

Re: issue with db links .. [message #334690 is a reply to message #334689] Thu, 17 July 2008 13:01 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

same dual works for me from other links ..
Re: issue with db links .. [message #334692 is a reply to message #334687] Thu, 17 July 2008 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post results from following SQL:

select count(*) from user_objects@testlink
Re: issue with db links .. [message #334696 is a reply to message #334687] Thu, 17 July 2008 13:16 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

From server:

SQL> select count(*) from user_objects@testlink;

COUNT(*)
----------
54

SQL>


From client:
07/17/2008 System@ Dmstest> select count(*) from user_objects@testlink;
select count(*) from user_objects@testlink
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11
ORA-02063: preceding 3 lines from TESTLINK


Elapsed: 00:00:01.03
07/17/2008 System@ Dmstest>


-Sai.



Re: issue with db links .. [message #334698 is a reply to message #334696] Thu, 17 July 2008 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start a new session, activate a 10046 trace, execute the statement, close the session and post the trace file.

Regards
Michel
Re: issue with db links .. [message #334848 is a reply to message #334696] Fri, 18 July 2008 06:09 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

From server:

SQL> select count(*) from user_objects@testlink;

From client:
07/17/2008 System@ Dmstest> select count(*) from user_objects@testlink;
select count(*) from user_objects@testlink


How did you create the database link? Did you give the tnsnames alias or the complete connect string? If you are using the alias in the tnsnames then it's worthwhile comparing the tnsnames.ora between the server and the client. Because if you fire a db-link query from the server then oracle will use the tnsnames definition in the server. If you fire the query from the client then it will use the tnsnames.ora from the client location.

Hope that helps.

Regards

Raj
Previous Topic: Prcedure created with Compilation error
Next Topic: Have to create folder in server from client machine through procedure
Goto Forum:
  


Current Time: Sat Dec 10 15:03:15 CST 2016

Total time taken to generate the page: 0.08617 seconds