Fine-tuning Oracle DG40DBC

Recently, a customer shared a workaround that resolved an issue that occurred when using our SQL Server ODBC driver to connect Oracle to SQL Server. The error the customer was getting was:

DELETE FROM mytable@MYLINKEDDATABASE
WHERE MyCol = 'MyValue';

17:01:01 [DELETE - 0 row(s), 0.000 secs] [Error Code: 2070, SQL State: 42000]
ORA-02070: database MYLINKEDDATABASE does not support some function in this context

and this was resolved by:

SQL> select fds_class_name from HS_FDS_CLASS;
ODBC11.2.0.2.0_0008
exec dbms_hs.replace_class_caps('ODBC11.2.0.2.0_0008', 564,'ODBC11.2.0.2.0_0008',564,
	                                                                 8191,NULL,1);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete

In Oracle, SELECT FDS_CLASS_NAME FROM HS_FDS_CLASS returns a string that identifies your DG4ODBC version. For example:

ODBC11.2.0.2.0_0008

Running this query:

SELECT * FROM HS_CLASS_CAPS WHERE FDS_CLASS_NAME ='ODBC11.2.0.2.0_0008';

returns a number of "capabilities" that show how DG4ODBC is configured to behave.

In the example shown earlier, the column the customer was an NVARCHAR type. Under the direction of Oracle support, the customer changes how DG4ODBC behaved with regards to this data type.

The customer found the relevant field in HS_CLASS_CAPS:

564 TO_NCHAR(op1) 0

and changed it with the Oracle package, DBMS_HS.ALTER_CLASS_CAPS.

Note that what the various DG4ODBC capabilities do are not, it seems, documented anywhere public. This blog cannot therefore provide specific guidance as to how to use them to resolve a specific problem. Instead, the blog is provided to make you aware that you have another tool at your disposal, if editing parameters in the DG4ODBC configuration file, initSID.ora does not resolve any issues you are having with your DG4ODBC connectivity.

Further information