Search code examples
sql-serveroracledblink

Select between Oracle and SQL Server via db_link

I have a query:

SELECT 
    b."Str" AS "State" into l_state_1C
FROM 
    "_InfoRg9050"@SQLSERVER.UISLAB.COM a
INNER JOIN 
    "EnumTexts"@SQLSERVER.UISLAB.COM b ON a."_Fld9052RRef" = b."_IDRRef"
WHERE 
    a."_Fld10998" = rec.decl_num_1c AND
    a."_Fld9059" = (select MAX(a."_Fld9059") 
                    from "_InfoRg9050"@SQLSERVER.UISLAB.COM a
                    inner join "EnumTexts"@SQLSERVER.UISLAB.COM b on a."_Fld9052RRef" = b."_IDRRef"     
                    where a."_Fld10998" = rec.decl_num_1c)
ORDER BY 
    a."_Fld9059" DESC;
  • Variable decl_num_1c has type varchar2(30)
  • Variable l_state_1C has type varchar(2014)
  • Column "Str" in table "EnumTexts" has type varchar(2014)

When decl_num_1c has "null" value I get an error

ORA-02070: Database SQLSERVER does not support SYS_OP_C2C in this context.

What I doing wrong ? Please help me.

Solution

  • In order to perform the query to a DB using a different character set Oracle will use the conversion function SYS_OP_C2C to convert the data. The message suggests that there is something that cannot be converted between the character sets.

    Check the data in the columns you are referencing to make sure that there is nothing that could cause problems.

    It can be painful to pin this down so . . . good luck.