Toad World® Forums

selecting over database link gives wrong results!


#1

select * from table@db_link

actually returns data from local table, if table with the same name (in current schema) exists locally. It even doesn’t check the link then!
create table at_test_link(n number);
insert into at_test_link values (1);
Now
select * from at_test_link@this_link_does_not_exist;
and this query returns one row!


#2

Hi Andre,

Unfortunately, we’re getting to the end of the cylcle so this issue will be address in 6.1.

Gwen


#3

Hi,
Strange, I can’t reproduce your issue, when i run query from table with non existing dblink i have standar error:
ORA-02019 connection description for remote database not found

Watch attachement

Ps, Maybe your oracle client is working wrong, try to do the same test in sqlplus…
clip0008.zip (73.3 KB)


#4

Hm, very strange.
I uninstalled 6.0 beta. Installed new beta build 1014. Restarted computer. No change.
I tried sqlplus (10.2.0.1), SQLNavigator 5.5.4, SQLDeveloper 1.5.1 - all of them work as expected. But SQLNavigator 6.0 is playing some game with me. Seems that i can’t select anything over any db link. And if table name before @ matches local table name, data is displayed from there, and anything can be after the @. If there is no local table with that name, ORA-00942: table or view does not exist is displayed.

Here are seven queries. The (correct) results with SQLNav 5.5.4 are in the first column and results with SQLNav 6.0 in the second.

— (1) local table - not used for anything, a “leftover”
select count(), min(id), max(id) from taotlused;
— (2) lets pick up an id an see how we are doing
select id from taotlused where id = 1492375;
— (3) this is frequently used
select id from taotlused@anette where id = 1492375;
— (4) this shouldn’t work
select id from taotlused@blabla where id = 1492375;
— (5) some more detail data from remote
select tao_id, tevs_id_on_pohitooks, taitmise_tunnus, taitmise_kp from tootellimused@anette where tao_id = 1492375;
— (6) this is a procedure that actually produces correct result, quering from the remote db
select l_synk_anette_anette_pkt.synk_anette2synk(1492375) from dual;
— (7) this is exactly what the above procedure does return using the link
select count(
)
from tootellimused@anette tt, taotlused@anette t
where tt.tao_id = t.id
and tt.tevs_id_on_pohitooks is null
and tt.taitmise_tunnus in (‘1’,‘2’,‘4’)
and t.id = 1492375
and tt.taitmise_kp is not null;

How to explain then that in SQLNav6.0 the query (3) brings back the row from the local database and seems the “where” is completely ignored. As (4) shows, everything is ignored starting from @. Queries (5) and (7) fail - no table named tootellimused exists locally. But (6) works - it queries tootellimused, but using a function.
EVen if i make it into an anonymous plsql block
declare
l_arv number;
begin
select count(*) into l_arv
from tootellimused@anette tt, taotlused@anette t
where tt.tao_id = t.id
and tt.tevs_id_on_pohitooks is null
and tt.taitmise_tunnus in (‘1’,‘2’,‘4’)
and t.id = 1492375
and tt.taitmise_kp is not null;
dbms_output.put_line(l_arv);
end;
/
it works - it prints the “1” out.

The result is pretty scary - nothing can be directly selected from any table over db link, i get a false error. AND, when i don’t get an error, i get a false result (from local database).

Any ideas?

The ORA messages still print out with a cube instead of line breaks (see picture), but that is another story and probably not relevant here.
sqlnav_b1014_p1.jpeg


#5

Charlie was probably using b979.

I retested it and found out it did work in 979 and 5.5. We will make a change to fix this issue.

Thanks for your feedbacks

Gwen