3.0.12 upgrade problem

After upgrade, I met this error messsage when conducting search...





ORA-29900: operator binding does not exist


ORA-06540: PL/SQL: compilation error


ORA-06553: PLS-907: cannot load library unit JCHEM.


CONTAINS_FUNC(reference by JCHEM.JC_CONTAINS)





Please help.

Did you execute the test.sh script as part of the installation of 3.0.12? Did it complete successfully?





Are you using JChem Cartridge in a schema different from the one where you installed it? Have you granted all the necessary privileges to the JChem Cartridge user?

Output of test.bat

Code:

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jun 21 11:13:12 2005 Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. Connected to: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production JCHEM_CORE_PKG.GETENVIRONMENT() ------------------------------------------------------------------------------ Oracle9i Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE    9.2.0.1.0       Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production JChem version in the database: 3.0.12 JChem version in the Tomcat server: 3.0.12 java.vm.version: 1.5.0-b64 java.vm.vendor: Sun Microsystems Inc. Apache Tomcat/5.5.4 JCHEM_CORE_PKG.GETENVIRONMENT() ------------------------------------------------------------------------------ Disconnected from Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production

They are installed under jchem scheme, same as before. Actually I used the same command to install 3.0.12 and 3.0.





There is only one user: jchem, which I used to install the database, and I can use this user to do search with 3.0.

I suggest to try and uninstall JChem Cartridge and reinstall it (3.0.12). Let me know if you see anything suspicious during uninstallation or installation.

I should use jcf_contains instead of jc_contains.

Whether you should use jcf_XXX instead of jc_XXX depends on where you need the given functionality. You will need jcf_XXX typically in PL/SQL procedures' bodies where the given functionality appears on the right side of an assignment instead of being part of a SQL statement. In such cases, you truly need a function instead of an operator. (Using an operator in such places in PL/SQL code results in a "PLS-00548: invalid use of operator." error message -- and not the error message you reported.)





On the other hand, in SQL statements you basically should always use an operator. Although a SQL statement like

Code:

select count(*) from nci_1k where jcf_contains(structure, 'c1ccccc1') = 1

will execute and return the expected result, execution will be slower by orders of magnitude than if you used the corresponding operator (jc_contains).





However, in an SQL statement like

Code:

select jcf_contains(structure, 'c1ccccc1') from nci_1k where  id = 311

whether you use the function or the operator has no performance impact.





The error message in your original posting suggests that you have a real low-level problem with the CONTAINS_FUNC PL/SQL function. Based on the error message, your problem does not seem to be a jc_XXX versus jcf_XXX issue. My guess is that something went wrong during uninstallation or installation.





A loosely related issue is the following (related to the use of jcf_XXX versus jc_XXX). I observed that Oracle 9.2.0.4 will crash, if I use the jc_standardize operator in SQL statements of the second kind above. This problem does not occur with Oracle 10g.





Apropos, why do you use Oracle 9.2.0.1.0? This is a pretty unstable version in my experience. More recent 9i patches are much more stable (even though they are also slower than 9.2.0.1.0). (10g beats all 9i patches in stability in my experience.)

I think I figured out the problem. The uninstall script actually failed to delete the operators, so I have to delete them manually, now it is working very well.





By the way, I found

Code:

select jc_molconvertbb(cd_smiles,'sdf -2') from ...

not working, is the syntax I'm using correct?





Thank you again for your help.

pkovacs wrote:

Whether you should use jcf_XXX instead of jc_XXX depends on where you need the given functionality. You will need jcf_XXX typically in PL/SQL procedures' bodies where the given functionality appears on the right side of an assignment instead of being part of a SQL statement. In such cases, you truly need a function instead of an operator. (Using an operator in such places in PL/SQL code results in a "PLS-00548: invalid use of operator." error message -- and not the error message you reported.) On the other hand, in SQL statements you basically should always use an operator. Although a SQL statement like Code: select count(*) from nci_1k where jcf_contains(structure, 'c1ccccc1') = 1 will execute and return the expected result, execution will be slower by orders of magnitude than if you used the corresponding operator (jc_contains). However, in an SQL statement like Code: select jcf_contains(structure, 'c1ccccc1') from nci_1k where  id = 311 whether you use the function or the operator has no performance impact. The error message in your original posting suggests that you have a real low-level problem with the CONTAINS_FUNC PL/SQL function. Based on the error message, your problem does not seem to be a jc_XXX versus jcf_XXX issue. My guess is that something went wrong during uninstallation or installation. A loosely related issue is the following (related to the use of jcf_XXX versus jc_XXX). I observed that Oracle 9.2.0.4 will crash, if I use the jc_standardize operator in SQL statements of the second kind above. This problem does not occur with Oracle 10g. Apropos, why do you use Oracle 9.2.0.1.0? This is a pretty unstable version in my experience. More recent 9i patches are much more stable (even though they are also slower than 9.2.0.1.0). (10g beats all 9i patches in stability in my experience.)

Code:

Code:

Code:

Code:

jc_molconvertbb takes a BLOB column as its first argument. Use cd_structure instead of cd_smiles.





Note, that the cd_structure column must be of type BLOB (instead of the traditional LONG RAW) and a jc_idxtype index must exist on cd_structure.