Data Type Checking Support for a Remote-Mapped Statement

The Oracle database has always performed data type checking and data type coercion in a homogeneous environment. For example, SELECT * FROM EMP WHERE EMPNO='7934' would return the same result as SELECT * FROM EMPNO WHERE EMPNO=7934. There is also full data type checking support for remote-mapped statements in a heterogeneous environment. In general, the operands in SQL statements whether its a column, literal, or bind variable would be processed internally for data type checking. Consider the following examples:

SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN='123'
SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN=CHAR_COLUMN;
SELECT * FROM EMP@LINK WHERE NUMBER_COLUMN=CHAR_BIND_VARIABLE;

Most non-Oracle databases do not support data type coercion, and the previous statements fail if they are sent to a non-Oracle database as is. The Heterogeneous Services component for the Oracle database performs data type checking and the necessary data type coercion before sending an acceptable statement to a non-Oracle database.

Data type checking provides consistent behavior on post-processed or remote-mapped statements. Consider the following two statements:

SELECT * FROM EMP@LINK WHERE TO_CHAR(EMPNO)='7933' + '1';

And:

SELECT * FROM EMP@LINK WHERE EMPNO='7933' + '1';

Both of the previous statements provide the same result and coercion regardless if the TO_CHAR function is supported in the non-Oracle database or not. Now, consider the following statement:

SELECT * FROM EMP@LINK WHERE EMPNO='123abc' + '1';

As data type checking is enforced, the coercion attempt within Oracle generates an error and returns it without sending any statements to a non-Oracle database.

In summary, there is consistent data type checking and coercion behavior regardless of post-processed or remote-mapped statements.