Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Are you sure the columns are varchar(36). In SQL Server this values would be uniqueidentifier normally. –  gbn Mar 21 '11 at 18:55
    
I was wrong. The columns are defined as Varchar(36) on the SQL Server side not uniqueidentifier even though they contain GUIDs. –  Leigh Riffel Mar 22 '11 at 14:07

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
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 Mar 22 '11 at 13:13
    
The materialized view described shows Varchar2(36) for both columns. –  Leigh Riffel Mar 22 '11 at 14:05
    
Any idea how I can diagnose this further or resolve it? –  Leigh Riffel Mar 25 '11 at 18:27
    
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. –  Gary Mar 27 '11 at 23:30
    
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. –  Leigh Riffel Mar 28 '11 at 0:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.