Oracle Database Server SQL Construct Processing

Heterogeneous Services and the gateway rewrite SQL statements when the statements need to be translated or postprocessed.

For the following examples, assume the INITCAP function is not supported in the non-Oracle database. Consider a program that requests the following from the non-Oracle database. For example:

SELECT "COLUMN_A" FROM "test"@remote_db
    WHERE "COLUMN_A" = INITCAP("COLUMN_B");

The non-Oracle database does not recognize the INITCAP function, so the Oracle database server fetches the data from the table test in the remote database and filters the results locally. The gateway rewrites the SELECT statement as follows:

SELECT "COLUMN_A", "COLUMN_B" FROM "test"@remote_db;

The results of the query are sent from the gateway to Oracle and are filtered by the Oracle database server.

If a string literal or bind variable is supplied in place of "COLUMN_B" as shown in the previous example, the Heterogeneous Services component of the Oracle server would apply the INITCAP function before sending the SQL command to the gateway. For example, if the following SQL command is issued:

SELECT "COLUMN_A" FROM "test"@remote_db WHERE "COLUMN_A" = INITCAP('jones');

The following SQL command would be sent to the gateway:

SELECT "COLUMN_A" FROM "test"@remote_db WHERE "COLUMN_A" = 'Jones';

Consider the following UPDATE request:

UPDATE "test"@remote_db SET "COLUMN_A" = 'new_value'
    WHERE "COLUMN_A" = INITCAP("COLUMN_B");

In this case, the Oracle database server and the gateway cannot compensate for the lack of support at the non-Oracle side, so an error is issued.

If a string literal or bind variable is supplied in place of "COLUMN_B" as shown in the preceding example, the Heterogeneous Services component of the Oracle server would apply the INITCAP function before sending the SQL command to the gateway. For example, if the following SQL command is issued:

UPDATE "test"@remote_db SET "COLUMN_A" = 'new_value' 
    WHERE "COLUMN_A" = INITCAP('jones');

The following SQL command would be sent to the gateway:

UPDATE  "test"@remote_db SET "COLUMN_A" = 'new_value' 
    WHERE "COLUMN_A" = 'Jones';

In previous releases, the preceding UPDATE statement would have raised an error due to the lack of INITCAP function support in the non-Oracle database.