OraFAQ Forum: Networking and Gateways » Remote query not working as expected.

Home » RDBMS Server » Networking and Gateways » Remote query not working as expected. (Oracle 11g R2 and MS SQL 2008 R2, and the win64 11gR2 gateway.)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Remote query not working as expected. [message #539239]

Thu, 12 January 2012 08:12

aceinc
Messages: 20
Registered: October 2009

Junior Member

I am using Oracle 11g R2 and MS SQL 2008 R2, and the win64 11gR2 gateway.

When I run the following query from the oracle database;

SELECT * FROM FACABS F WHERE lpad(to_char(f.FACABS_FACILITY_ABSTRACTOR),10,'0') not in (select "Value" from userfacilities@webplus);

I expect to get a list of all records in FACABS that does not have a match in userfacilities@webplus. Instead I get a list of all records in FACABS irrespective of their existence in userfacilities@webplus.

If I run the query;

select "Value" from userfacilities@webplus

I get a list of all "Value"s from userfacilities@webplus. If I take some of the valuse returned by the previous query and create a query like;

SELECT * FROM FACABS F WHERE lpad(to_char(F.FACABS_FACILITY_ABSTRACTOR),10,'0') not in ('0000008736','0000008731','0000008727');

I get a list reduced by the values in the "not in" clause.

Is it possible to use database links in a subquery?

I was unable to find much in the way of help in writing real life queries using database links. If someone knows of some documentaion, preferably with examples, I would appreciate it.

Report message to a moderator

Re: Remote query not working as expected. [message #539242 is a reply to message #539239]

Thu, 12 January 2012 08:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>SELECT * FROM FACABS F WHERE lpad(to_char(f.FACABS_FACILITY_ABSTRACTOR),10,'0') not in (select "Value" from userfacilities@webplus);

post DDL for both FACABS & userfacilities@webplus tables

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Remote query not working as expected. [message #539245 is a reply to message #539239]

Thu, 12 January 2012 08:33

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Is it possible to use database links in a subquery?

Yes.

SQL> select * from dual where dummy in (select dummy from dual@mylink);
D
-
X

1 row selected.

SQL> select * from dual where dummy not in  (select dummy from dual@mylink);

no rows selected

Regards
Michel

Report message to a moderator

Re: Remote query not working as expected. [message #539246 is a reply to message #539242]

Thu, 12 January 2012 08:35

aceinc
Messages: 20
Registered: October 2009

Junior Member

The relevant pieces are;

SQL table - userfacilities - [Value] [nvarchar](10) NOT NULL

Oracle table - FACABS - FACABS_FACILITY_ABSTRACTOR VARCHAR2(10)

The oracle table does not have leading zeroes, the sql table does.

Report message to a moderator

Re: Remote query not working as expected. [message #539248 is a reply to message #539246]

Thu, 12 January 2012 08:38

aceinc
Messages: 20
Registered: October 2009

Junior Member

In my example should the subquery be processed before the rest of the query?

It behaves as though it is not.

Report message to a moderator

Re: Remote query not working as expected. [message #539251 is a reply to message #539248]

Thu, 12 January 2012 08:55

aceinc
Messages: 20
Registered: October 2009

Junior Member

Perhaps the following would make it easier to understand. Consider the following queries and their results;

SQL> SELECT FACABS_FACILITY_ABSTRACTOR FROM FACABS F WHERE lpad(f.FACABS_FACILITY_ABSTRACTOR,10,'0') in ('0000008736','0000008731','0000008727');

FACABS_FAC
----------
8736
8731
8727

SQL> select "Value" from userfacilities@webplus where "Value" in ('0000008736','0000008731','0000008727');

Value
--------------------
0000008736
0000008731
0000008727

SQL> SELECT FACABS_FACILITY_ABSTRACTOR FROM FACABS F WHERE lpad(f.FACABS_FACILITY_ABSTRACTOR,10,'0') in (select "Value" from userfacilities@webplus where "Value" in ('0000008736','0000008731','0000008727'));

no rows selected

I qualified things to reduce the amount of data returned. You will notice that both tables have the same "key", but when I try to use the subquery to extract the data from the facabs table it doesn't find the records.

Report message to a moderator

Re: Remote query not working as expected. [message #539252 is a reply to message #539251]

Thu, 12 January 2012 09:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

aceinc wrote on Thu, 12 January 2012 06:55

Perhaps the following would make it easier to understand. Consider the following queries and their results;

SQL> SELECT FACABS_FACILITY_ABSTRACTOR FROM FACABS F WHERE lpad(f.FACABS_FACILITY_ABSTRACTOR,10,'0') in ('0000008736','0000008731','0000008727');

FACABS_FAC
----------
8736
8731
8727

above has me confused & perplexed.
It is as though Oracle is treating the results as a NUMBER.

strings can have leading zeros but numbers do not

  1* select '0000008736','0000008731','0000008727' , 0000009876 from dual
SQL> /

'000000873 '000000873 '000000872 0000009876
---------- ---------- ---------- ----------
0000008736 0000008731 0000008727       9876

[Updated on: Thu, 12 January 2012 09:11]

Report message to a moderator

Re: Remote query not working as expected. [message #539255 is a reply to message #539252]

Thu, 12 January 2012 09:31

aceinc
Messages: 20
Registered: October 2009

Junior Member

The FACABS (oracle) table has a varchar2 field, but it does not contain leading zeroes in the data. Which is why I lpad it when doing the comparison.

So the MSSQL table contains '0000008736' and the oracle table contains '8736' both are string fields.

In a perfect world both tables would have the same data types and rules for the data, and in a perfect world everyone would like the beer I like.

Report message to a moderator

Re: Remote query not working as expected. [message #539258 is a reply to message #539255]

Thu, 12 January 2012 09:54

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Try

With data as ( select /*+ materialize */ "Value" from userfacilities@webplus)
select  * FROM FACABS F 
WHERE to_char(f.FACABS_FACILITY_ABSTRACTOR, 'fm0000000000') not in (select * from data)

Regards
Michel

Report message to a moderator

Re: Remote query not working as expected. [message #539261 is a reply to message #539258]

Thu, 12 January 2012 11:10

aceinc
Messages: 20
Registered: October 2009

Junior Member

Michel:

When I execute;

SQL> select count(*) FROM FACABS F
2 WHERE f.facabs_facility_abstractor between '0000' and '9999';

COUNT(*)
----------
2844

SQL> With data as ( select /*+ materialize */ "Value" from userfacilities@webplu
s)
2 select count(*) FROM FACABS F
3 WHERE f.facabs_facility_abstractor between '0000' and '9999' and to_char(f.FACABS_FACILITY_ABSTRACTOR, 'fm0000000000') not in (select * from data);

COUNT(*)
----------
2844

I added the "between" clause because the FACABS_FACILITY_ABSTRACTOR field had some alpha characters that will cause the format on the to_char to blow up other wise.

You notice the counts are the same, but, the second one should be about 400 less than the first if it were actually seeing the data from the mssql DB.

Report message to a moderator

Re: Remote query not working as expected. [message #539262 is a reply to message #539261]

Thu, 12 January 2012 11:21

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you say so...
There is something different in the values, check for spaces or not printable character or...

Post result of:

select '"'||"Value"||'"', dump("Value") from userfacilities@webplus where rownum <= 10;

Regards
Michel

Report message to a moderator

Re: Remote query not working as expected. [message #539268 is a reply to message #539262]

Thu, 12 January 2012 12:32

aceinc
Messages: 20
Registered: October 2009

Junior Member

Michel:

You seem to be on to something. It seems as though the subquery returns a null between each character.

Now the questions are "Why?" and "How do we fix it?"

Could it have something to do with some form of character set difference? It returns multibyte, when I am expecting single byte characters.

I am trying to fiddle with "HS_KEEP_REMOTE_COLUMN_SIZE=ALL" but I have issues closing the DBLink, and reopening it.

SQL> select '"'||"Value"||'"', dump("Value") from userfacilities@webplus where rownum <= 10;

'"'||"VALUE"||'"'
----------------------
DUMP("VALUE")
--------------------------------------------------------------------------------

" 0 0 0 0 0 0 0 0 0 0"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48,0,48

" 1 1 1 1 1 1 1 1 1 1"
Typ=1 Len=20: 0,49,0,49,0,49,0,49,0,49,0,49,0,49,0,49,0,49,0,49

" 2 2 2 2 2 2 2 2 2 2"
Typ=1 Len=20: 0,50,0,50,0,50,0,50,0,50,0,50,0,50,0,50,0,50,0,50

'"'||"VALUE"||'"'
----------------------
DUMP("VALUE")
--------------------------------------------------------------------------------

" 0 0 0 0 0 0 8 7 3 6"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,51,0,54

" 0 0 0 0 0 0 8 7 3 1"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,51,0,49

" 0 0 0 0 0 0 8 7 2 7"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,50,0,55

'"'||"VALUE"||'"'
----------------------
DUMP("VALUE")
--------------------------------------------------------------------------------

" 0 0 0 0 0 0 8 7 2 6"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,50,0,54

" 0 0 0 0 0 0 8 7 2 5"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,50,0,53

" 0 0 0 0 0 0 8 7 2 3"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,50,0,51

'"'||"VALUE"||'"'
----------------------
DUMP("VALUE")
--------------------------------------------------------------------------------

" 0 0 0 0 0 0 8 7 2 0"
Typ=1 Len=20: 0,48,0,48,0,48,0,48,0,48,0,48,0,56,0,55,0,50,0,48

10 rows selected.

Report message to a moderator

Re: Remote query not working as expected. [message #539269 is a reply to message #539268]

Thu, 12 January 2012 12:38

aceinc
Messages: 20
Registered: October 2009

Junior Member

The following seems to work, but I would really lie to fix it the right way;

With data as ( select /*+ materialize */ "Value" from userfacilities@webplus)
select count(*) FROM FACABS F
WHERE f.facabs_facility_abstractor between '0000' and '9999' and to_char(f.FACABS_FACILITY_ABSTRACTOR, 'fm0000000000') not in (select trim("Value") from data);

Note the "(select trim("Value") from data)"

Report message to a moderator

Re: Remote query not working as expected. [message #539271 is a reply to message #539268]

Thu, 12 January 2012 12:57

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Now the questions are "Why?" and "How do we fix it?"

Because Windows works in Unicode (UTF16) and so all characters are on 2 bytes.
So use REPLACE to remove the chr(0):

select replace("Value",chr(0)) from userfacilities@webplus

Regards
Michel

Report message to a moderator

Re: Remote query not working as expected. [message #539278 is a reply to message #539271]

Thu, 12 January 2012 14:28

aceinc
Messages: 20
Registered: October 2009

Junior Member

I suspect what you suggest will probably work, but if I am going to write many queries using data from both systems, doing these replaces on these fields seems inefficient from both a DB as well as a programmer point of view.

How can I configure the DBLink so that the data comes across properly?

Report message to a moderator

Re: Remote query not working as expected. [message #539286 is a reply to message #539278]

Fri, 13 January 2012 00:49

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There is no way at database link level, maybe at HS one but I don't know.
What is your database character set?

select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

Regards
Michel

Report message to a moderator

Re: Remote query not working as expected. [message #539338 is a reply to message #539286]

Fri, 13 January 2012 10:00

aceinc
Messages: 20
Registered: October 2009

Junior Member

The character set is WE8MSWIN1252

Report message to a moderator

Re: Remote query not working as expected. [message #539340 is a reply to message #539338]

Fri, 13 January 2012 10:36

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please ALWAYS copy and paste the SQL*Plus session.
And please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

I don't know many things about gateways, maybe there is some parameters to set at this level.
What is strange for me is that Oracle does not convert from UTF16 to WE8MSWIN1252. It seems it thinks the remote database is in the same character set (or at least a 8-bit one).
Are you sure you executed the statement I posted?

Regards
Michel

Report message to a moderator

Re: Remote query not working as expected. [message #539359 is a reply to message #539340]

Fri, 13 January 2012 19:54

aceinc
Messages: 20
Registered: October 2009

Junior Member

Yes

Report message to a moderator

Re: Remote query not working as expected. [message #539360 is a reply to message #539359]

Fri, 13 January 2012 19:59

aceinc
Messages: 20
Registered: October 2009

Junior Member

BTW, I do most of my work in SQLDeveloper, so there is generally no "session." When it is important I do post the query & the results, and take my time to post the entire SQLPLUS session. In this case, you asked a question, and even though I had similar query that yields the same results, I cut & pasted yours, and then answered your question.

I appreciate the time that you have taken to help me, and I try to make sure that I provide reasonable information so that I get reasonable responses.

Report message to a moderator

Previous Topic:	ora-12170:TNS Connect TimeOut Error
Next Topic:	ORA-12170: TNS:Connect timeout occurred

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 18:19:50 CST 2025