operator binding errors

Hi,





I am encountering the following error when attempting to perform a chemical search in SQLplus. The chemical database was created with instantJchem.





E:\JChem\cartridge>test jchem/jchem





SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 6 07:41:36 2007





Copyright (c) 1982, 2005, Oracle. All rights reserved.








Connected to:


Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production


With the Partitioning, OLAP and Data Mining options








JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------





Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod


PL/SQL Release 10.2.0.1.0 - Production


CORE 10.2.0.1.0 Production


TNS for 32-bit Windows: Version 10.2.0.1.0 - Production


NLSRTL Version 10.2.0.1.0 - Production


NLSRTL Version 10.2.0.1.0 - Production


JChem version in the database: 3.2.6


JChem version in the Tomcat server: 3.2.6


java.vm.version: 1.6.0_01-b06


java.vm.vendor: Sun Microsystems Inc.


Apache Tomcat/6.0.1





JCHEM_CORE_PKG.GETENVIRONMENT()


--------------------------------------------------------------------------------





Major JDBC version in Tomcat: 10


Minor JDBC version in Tomcat: 2








Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr


oduction


With the Partitioning, OLAP and Data Mining options


E:\JChem\cartridge>








JChem version: 3.2.6


JChem Streams version: 3.2.6














SQL> select cd_id from bionet where jc_contains(cd_structure,'CN1C=NC2=C1C(=O)N(C)C(=O)N2C')=1;


select cd_id from bionet where jc_contains(cd_structure,'CN1C=NC2=C1C(=O)N(C)C(=O)N2C')=1


*


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_CONTAINS'





I also tried this from a different post





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';





select cd_id from bionet where jc_containsb(cd_structure, charToTmpBlob('CCCCC')) = 1;





with the following error when executing


*


ERROR at line 1:


ORA-29531: no method stringToBlob in class


chemaxon/jchem/cartridge/JCFunctionsBlob





Can someone suggest how I can perform the same search in instantJchem at the SQL Level?

Hi,





The operator jc_contains has three bindings:

Code:

(VARCHAR2, VARCHAR2) (CLOB, CLOB) (BLOB, BLOB)

.





Based on the error message, the cd_structure column of the table bionet is probably of type BLOB. You might then want to cast the query parameter to raw, so the (BLOB, BLOB) binding is used by the Oracle parser:

Code:

select count(*) from bionet where jc_contains(cd_structure, utl_raw.cast_to_raw('CN1C=NC2=C1C(=O)N(C)C(=O)N2C'))=1;

Conversely, if the cd_structure column was of type CLOB, you could use:

Code:

select count(*) from bionet where jc_compare(cd_structure, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C', 't:s') = 1;

the jc_compare operator having a (CLOB, VARCHAR2, VARCHAR2) binding as well.





Note that you'll need to create a jc_idxtype index on the cd_structure column in any case before searching with JChem Cartridge:

Code:

create index jcxbionet on bionet(cd_structure) indextype is jchem_326.jc_idxtype

where jchem_326 is the schema where jchem cartridge has been installed.





Thanks


Peter

You mentioned Instant JChem in your question.


I'm nore sure about the significance of this as Instant JChem does not yet have direct support for the JChem cartridge, only JChem base.


You might be able to run the equivalent search using the Instant JChem query builder, but you can't run queries directly at the SQL level.





Please explain more if I have misunderstood.





Tim

Thanks for your quick reply. I ran into another error when trying to create the index on the bionet(cd_structure) field.





SQL> create index jcxbionet on bionet(cd_structure) indextype is jc_idxtype;


create index jcxbionet on bionet(cd_structure) indextype is jc_idxtype


*


ERROR at line 1:


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.RuntimeException: The index on table 'JCHEM.BIONET' contains obsolete


data. (Current index version: 37, index version for 'JCHEM.BIONET': 36.) Please


recreate the index with the 'regenerateTable' option.


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


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 17








SQL> create index jcxbionet on bionet(cd_structure) indextype is jc_idxtype para


meters('regeneratetable');


create index jcxbionet on bionet(cd_structure) indextype is jc_idxtype parameter


s('regeneratetable')


*


ERROR at line 1:


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.IllegalArgumentException: Illegal option: regeneratetable


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


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 17





Any suggestions?

Please, specify the value 'true' for the index parameter:

Code:

create index jcxbionet on bionet(cd_structure) indextype is jchem_326.jc_idxtype parameters('regenerateTable=true');

(As a matter of fact, it does not really matter what value you specify. You just have to specify a value in order to trigger regeneration. Nonsensically, even with 'regenerateTable=false', the table will be regenerated.)





This is a documentation bug and a bug in the error message as well. It will be fixed in the next JChem release.





Thanks


Peter

Thanks! The index was created successfully and searches executed.