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.)
Remote query not working as expected. [message #539239] Thu, 12 January 2012 08:12 Go to next message
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.


Re: Remote query not working as expected. [message #539242 is a reply to message #539239] Thu, 12 January 2012 08:22 Go to previous messageGo to next message
BlackSwan
Messages: 26747
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/
Re: Remote query not working as expected. [message #539245 is a reply to message #539239] Thu, 12 January 2012 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Remote query not working as expected. [message #539246 is a reply to message #539242] Thu, 12 January 2012 08:35 Go to previous messageGo to next message
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.
Re: Remote query not working as expected. [message #539248 is a reply to message #539246] Thu, 12 January 2012 08:38 Go to previous messageGo to next message
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.
Re: Remote query not working as expected. [message #539251 is a reply to message #539248] Thu, 12 January 2012 08:55 Go to previous messageGo to next message
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.
Re: Remote query not working as expected. [message #539252 is a reply to message #539251] Thu, 12 January 2012 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 26747
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 Go to previous messageGo to next message
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.
Re: Remote query not working as expected. [message #539258 is a reply to message #539255] Thu, 12 January 2012 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Remote query not working as expected. [message #539261 is a reply to message #539258] Thu, 12 January 2012 11:10 Go to previous messageGo to next message
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.
Re: Remote query not working as expected. [message #539262 is a reply to message #539261] Thu, 12 January 2012 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Remote query not working as expected. [message #539268 is a reply to message #539262] Thu, 12 January 2012 12:32 Go to previous messageGo to next message
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.
Re: Remote query not working as expected. [message #539269 is a reply to message #539268] Thu, 12 January 2012 12:38 Go to previous messageGo to next message
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)"
Re: Remote query not working as expected. [message #539271 is a reply to message #539268] Thu, 12 January 2012 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Remote query not working as expected. [message #539278 is a reply to message #539271] Thu, 12 January 2012 14:28 Go to previous messageGo to next message
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?
Re: Remote query not working as expected. [message #539286 is a reply to message #539278] Fri, 13 January 2012 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Remote query not working as expected. [message #539338 is a reply to message #539286] Fri, 13 January 2012 10:00 Go to previous messageGo to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
The character set is WE8MSWIN1252
Re: Remote query not working as expected. [message #539340 is a reply to message #539338] Fri, 13 January 2012 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67464
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Remote query not working as expected. [message #539359 is a reply to message #539340] Fri, 13 January 2012 19:54 Go to previous messageGo to next message
aceinc
Messages: 20
Registered: October 2009
Junior Member
Yes
Re: Remote query not working as expected. [message #539360 is a reply to message #539359] Fri, 13 January 2012 19:59 Go to previous message
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.
Previous Topic: ora-12170:TNS Connect TimeOut Error
Next Topic: ORA-12170: TNS:Connect timeout occurred
Goto Forum:
  


Current Time: Sun Oct 25 06:35:00 CDT 2020