Index and Search with JChem Cartridge - ChemAxon Forum Archive

User dfeb81947d

14-10-2005 12:41:26

Dear Support,

I'm trying JChem Cartridge 3.0.14

with Oracle 9.2 release 5 and JDK 1.4.2_05

If I do:

Code:

select count(*) from table_csmol

where jc_contains(cd_smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

it works fine.

I tried to search on Structure column as:

Code:

select count(*) from table_csmol

where jc_contains(cd_structure, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

I have the following exception:

Quote:

ERROR at line 1:

ORA-20101: Please, create domain index on the column referenced in the operator

jc_contains of the table with DATA_OBJECT_ID=50281. You can find out the name

of the table by executing:

SELECT owner, object_name INTO schema_name, table_name

FROM sys.dba_objects WHERE DATA_OBJECT_ID = 50281

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 73

ORA-06512: at "JCHEM.CONTAINS_FUNC", line 7

So I try to create an Index on cd_structure:

Code:

CREATE INDEX jc_idx_gpcr_struc ON table_csmol(cd_structure) INDEXTYPE IS jc_idxtype;

and I have the following exception:

Quote:

ERROR at line 1:

ORA-29867: cannot create a domain index on a LONG column

on my table TABLE_CSMOL fields for structures are by default: LONG RAW.

Is it possible to make a structure search on the structure table?

Thank you for your help,

Warm regards,

Jacques

ps: I think it cross the questions of yuhong_wang

http://www.chemaxon.com/forum/ftopic951.html

http://www.chemaxon.com/forum/ftopic950.html

ChemAxon aa7c50abf8

14-10-2005 13:04:11

Dear Jacques,

Please, read the following threads:

http://www.chemaxon.com/forum/ftopic735.html

http://www.chemaxon.com/forum/ftopic739.html

Peter

User dfeb81947d

14-10-2005 13:41:11

Dear Peter,

Thank you very much.

With Blob it should works fine.

Code:

SQL> select count(*) from aurquest.gpcr_csmol

where jc_contains(cd_structure, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;

I have the following message

Quote:

ERROR at line 1:

ORA-29900: operator binding does not exist

ORA-06553: PLS-306: wrong number or types of arguments in call to 'JC_CONTAINSB'

Is there a function to convert VARCHAR2 into Blob, to try structure search in SQLplus

If I use Java and PreparedStatement, I won't have any problem to Convert String to Blob, but with SQLplus?

Thank you for your help

Best Regards

Jacques

ps: I have another question regarding the PL/SQL functions:

If I want to do Exact, Sub, Similarity SearchI would use:

jc_contains, jc_equals, jc_tanimoto

or just jc_compare with the good options?

Does they do the same?

ChemAxon aa7c50abf8

17-10-2005 15:04:10

Dear Jacques,

Quote:

Is there a function to convert VARCHAR2 into Blob, to try structure search in SQLplus

Create a function like this:

Code:

create or replace function charToTmpBlob(c varchar2) RETURN BLOB AS LANGUAGE JAVA NAME 'chemaxon.jchem.cartridge.JCFunctionsBlob.stringToTmpBlob(java.lang.String) return oracle.sql.BLOB';

You can use this function like:

Code:

select count(*) from jcb_nci_12n where jc_containsb(cd_structure, charToTmpBlob('c1ccccc1')) = 1;

Quote:

If I use Java and PreparedStatement, I won't have any problem to Convert String to Blob, but with SQLplus?

You can do it with the above function.

Quote:

If I want to do Exact, Sub, Similarity SearchI would use:

jc_contains, jc_equals, jc_tanimoto

or just jc_compare with the good options?

Yes, jc_compare covers all the search functionalities you mentioned.

Peter