Oracle query against SQL Server database giving inconsistent results

Question

We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database. A particular table in the SQL Server database has two Varchar(36) columns. The following statement returns 1292 rows:

select "id", "folder_id" from dbo.mediasite_presentation@ms;

Querying for a specific value in the first column returns a row:

select "id", "folder_id" from dbo.mediasite_presentation@ms 
 where "id"='006d815d-6e9e-4004-9104-51213a1ecd52';
id folder_id
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 54aa9b6e-1c55-4de5-a06f-033d8b19fff0

But querying for a specific value in the second column returns no rows:

select "id", "folder_id" from dbo.mediasite_presentation@ms 
 where "folder_id"='54aa9b6e-1c55-4de5-a06f-033d8b19fff0';
no rows selected

Using the where clause that works and wrapping each field in a to_char returns the following interesting results:

select to_char("id"), to_char("folder_id") from dbo.mediasite_presentation@ms 
 where "id"='006d815d-6e9e-4004-9104-51213a1ecd52';
TO_CHAR("ID") TO_CHAR("FOLDER_ID")
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 ┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐

Interestingly enough I can produce the same results just by adding a rownum clause:

select "id", "folder_id" from dbo.mediasite_presentation@ms 
 where "id"='006d815d-6e9e-4004-9104-51213a1ecd52' and rownum<=1;
id folder_id
------------------------------------ ------------------
006d815d-6e9e-4004-9104-51213a1ecd52 ┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐

Even stranger is the fact that creating a materialized view for query that returns no data actually returns data.

Create Materialized View TempMV as (
 select "id", "folder_id" from dbo.mediasite_presentation@ms 
 where "folder_id"='54aa9b6e-1c55-4de5-a06f-033d8b19fff0' and rownum<=1);
Materialized view created.
select * from TempMV;
id folder_id
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 54aa9b6e-1c55-4de5-a06f-033d8b19fff0

Please answer even if you can only explain a piece of this puzzle.

• gbn: Are you sure the columns are varchar(36). In SQL Server this values would be uniqueidentifier normally.
• Leigh Riffel: I was wrong. The columns are defined as Varchar(36) on the SQL Server side not uniqueidentifier even though they contain GUIDs.

Answer 1

I'd suspect some for of characterset conversion issue. When you do a TO_CHAR, you are getting back "┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐" (18 characters). That suggests that there is some confusion about whether the data contained in the column is single-byte or multi-byte.

"The uniqueidentifier data type in SQL Server is stored natively as a 16-byte binary value" This should ideally come across to Oracle as a RAW datatype not a VARCHAR2 or CHAR.

If you do a

CREATE VIEW temp_view as select "id", "folder_id" from dbo.mediasite_presentation@ms

what shows up when you do a DESC temp_view

• Leigh Riffel: When I create the view a describe shows the id column as Varchar2(36) and the folder_id column as NVarchar2(18) just as a direct describe on the remote table does.
• Leigh Riffel: The materialized view described shows Varchar2(36) for both columns.
• Leigh Riffel: Any idea how I can diagnose this further or resolve it?
• Gary: All I can think of would be something on the SQL Server side to convert the 16-byte binary to a character representation. That should only contain the alphanumeric characters and hopefully they'll get correctly translated. On the Oracle side you could look at UTL_RAW and see if you can get back to raw bytes in some way.
• Leigh Riffel: You may have missed my comment above. These fields do not use the uniqueidentifier type on the SQL Server side even though they contain a GUID. They are plain Varchar(36), yet one works as expected on the Oracle side and the other doesn't.