Can I use JChem Cartridge with DBLink?

Quote:

Can I use JChem Cartridge with DBLink?

Yes, you can, but the usual (non JChem Cartridge specific) limitations on dblink and remote objects apply.




Assume that you have created a dblink from the local database to a remote database instance called negev:

Code:

SQL> create public database link negev using 'negev';

Structure search will work with VARCHAR2 structure columns similarly to how it works locally. For standard structure tables (structure is a VARCHAR2 column):

Code:

SQL> select structure from nci_10k@negev where jc_compare@negev(structure, 'Brc1ccccc1', 't:s') = 1

For JChem structure tables:

Code:

SQL> select count(*) from jc_nci_10k@negev where jc_compare@negev(cd_smiles, 'c1ccccc1', 't:s') = 1

(The example for the JChem structure table assumes that you have created the jc_idxtype index on the cd_smiles column.)




Search over dblink referencing a remote LOB column will fail:

Code:

SQL> select cd_structure from jc_nci_10k@negev where jc_compare@negev(cd_structure@negev, 'c1ccccc1', 't:s') = 1                                                                       * ERROR at line 1: ORA-22992: cannot use LOB locators selected from remote tables

The workaround for LOB columns is to create wrapper stored functions in the remote database, let them reference the LOBs in question and have the remote functions return the values you are interested in. If you are interested in the matching structures themselves, you have two options:

* either have the remote functions return the structures piece-meal as VARCHAR2 or RAW type


* or if you use Oracle 10gR2 or newer and use either PL/SQL, OCI or JDBC, you can use the Oracle Data Interface: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm#sthref968