Comparison with string literals

When the TO_NCHAR capability is enabled, the string comparison for Unicode String columns is delegated to the remote query. However, comparison for Variant as String columns with string literals will generate the following or similar error message:

Here is an example query:

SELECT e."Name" Element, ea."Name" Attribute,
ea."Value"
FROM Element.Element@pi e
INNER JOIN Element.Attribute@pi ea ON ea."ElementID" = e.ID
WHERE ea."Name" = 'Plant'
AND ea."Value" Like N'H%'
ORDER BY e."Name", ea."Name";

Here is the resulting error message:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
No suitable function overload for 'Like' could be found. {42000,NativeErr = -1}
ORA-02063: preceding 2 lines from PI
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.

This error is due to the fact that the underlying Real-Time Query Processing (RTQP) Engine requires an explicit cast between the two data types. The fact that the ODBC driver exposes the Value column as String is not known to the RTQP Engine. Unfortunately, an explicit cast in the query can only be done using Oracle syntax so that the cast is performed locally and this will lead to post-processing.

Note: The error results from the Value column only. The Name column is a native Unicode String column and has no such issue.

An explicit cast in the Oracle query is one solution but results in post-processing:

SELECT e."Name" Element, ea."Name" Attribute,
ea."Value"
FROM Element.Element@pi e
INNER JOIN Element.Attribute@pi ea ON ea."ElementID" = e.ID
WHERE ea."Name" = 'Plant'
AND cast(ea."Value" as nvarchar2(50)) Like N'H%'
ORDER BY e."Name", ea."Name";