Toad World® Forums

Sqlnav loss connection while selecting unusable dblink


#1

Hi!
I found something like that,it looks like sqlnav is loosing connection while sql is using invalid dblink. Error occurs only when tns exists and representing database wich is down.

Here are the steps to reproduce this situation:

  1. Lets add virtual database into tnsnames.ora:
    –add this into tnsnames.ora
    TEST_LINK =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TEST_HOST)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = TEST)
    )
    )

  2. Create DBLINK using this database connection:
    —then create dblink

CREATE DATABASE LINK TEST_LINK
CONNECT TO A IDENTIFIED BY A
USING ‘TEST_LINK’
3. Try execute this sql:
–execute following
select sysdate from dual@TEST_LINK

Afther that reconnect dialog is showed, but why? When you choose “not recconnect” your session is gone. Correct error message is showed afther reconnect.

Try execute following in sqlplus, here everything works fine.

PS.
Jaime I tasted FOSTERS beer - not too bad :wink:

Regards Piter


#2

Hi Piter,
Thanks for sending the info.

I have tried to reproduce the issue here but could not manage to get the lost connection problem as per your note.
I got Ora error messages in the output window but the connection remains open:
0:05:16 ORA-12154: TNS:could not resolve the connect identifier specified
0:05:16 *** SCRIPT END : Session:BRUCE@NAV10G22(1) 26-Sep-2006 0:05:16 ***
0:05:16 End SQL Editor Execution

I will discuss with the team tomorrow if we could identify anything else…
Jaime will get back to you on this plus more Oz beer brands which you can try .

Thanks and regards,
Bruce


#3

Hi Bruce!
Your connection descryption must exist in tnsnames.ora, i its true, oracle should return: ORA-12545: Connect failed because target host or object does not exists.

You created dblink with TNS which was not found in tnsnames.ora.

Watch attachement.

Regards Piter
untitled-2 copy.jpeg


#4

Hi Piter,

We got the experts onto it… Andrew and Sekhar have replicated your issue, and you have indeed found a bug!! Good one new dad
I’ve logged a CR for this, and we will run it through CCB and get a priority assigned to it.

Piter, do you consider this to be a likely scenario that will affect many NAV users?
How did you find the problem… was it a legitimate workflow?

Jaime


#5

By the way…
Now that you’ve tried Fosters, you need to track down some Carlton Draught or Crown Lager. Either of those will blow Fosters Lager out of the water!!!
If you like Fosters, you will LOVE those ones

Jaime


#6

HI!
I don’t think so that this issue is knonwn by many users, situation with dblink linked to downed/not existing host is not often. My customer’s warehouse imports date from many remote databases, via dblink. Loading Sqls are generated automaticly, and sometimes it happends that some hosts are down or disconnected from net. Opening link is the simplest test to know that host lives. Yesterday i was correcting plsql code which is doing that test, that’s why I found this issue. I have been seen this disconnecting sometimes in the past, but i Didn’t reseat this disconecting with exactly ORA-12545 Error, radher I thought that this session is disconnected randomly by Oracle not by Sqlnav, and yesterday i checked this. And the rest you know.

Know about beers!
Jaime I will find these beers, my wife likes fosters, today i bought e new one.

Jaime why are you MisuBisu?? Because Mitsubischi?


#7

Hi Piter,

We have managed to track down the issue. Changes have been already made to stop the conx disconnect. The changes will be in the next beta build.

Thanks again for your input on this .

Regards,
Bruce


#8

Nice to hear that, heh you have turbo trouble resolver!

Piter


#9

Now this is not so easy to answer…
My partner calls me ‘Ms Bis’ (or ‘Bis’ for short).
She calls me this, as we both have the name Jaime (how we met is another story).
I have a Japanese friend, and they say ‘Misu Bisu’ rather than ‘Ms Bis’…
When I was after a forum name for internet sites etc., I decided to use ‘MisuBisu’ as it was always available (no one else was using it). So it just sort of happened!!
Definitely not related to Mitsubishi!! My preferred car is Porsche. You can see mine at: http://jaime.net/928 or look at the attachment (me and my partner).
snow.jpeg


#10

Great CAR Jaime!!!
I was asking because I have mitsubishi and next car which i want to buy will be mitsubishi too!!