Hello Tim & Co,
I upgraded our JChem installation from 5.3.3 to 5.3.5 just now and the cartridge upgrade seemed to go OK according to the script output (attached). Post upgrade, general text searching within IJC 5.3.4 is OK but structure searching (error text attached) gives an immediate error - I haven't see this one before - any ideas?
KR & TIA,
Which version of IJC are you using for this?
I suspect you are using 5.3.1, and need to upgrade to 5.3.4 so that the JChem tables are compatible with the cartridge version you upgraded to?
Definitely IJC 5.3.4, on Mac & PC...
could you check if you have a table called JCHEMPROPERTIES_CR (or whatever name you gave the property table plus _CR) and see if it has a CACHE_ID column.
Also, for all structure tables, and JChem cartridge indexes look for the table named *_UL and see if it has a CACHE_ID column,
Something seems to be wrong with one or the other of these.
The problem is that the upgrade script fails to recognize that indices need to be upgraded. You have to manually rebuild the indices:
ALTER INDEX <index-name> REBUILD
This bug will be fixed in the next minor release.
Sorry for the inconvenience.
Thanks for the replies again.
Tim: The JCHEMPROPERTIES_CR table exists and it has a CACHE_ID field. Most of the *_UL tables have a CACHE_ID field but some don't, can't see a pattern at this stage...
Peter: Which indexes are we talking about - all the indexes in the IJC schema?
KR & TIA,
Which indexes are we talking about - all the indexes in the IJC schema?
All the jc_idxtype indices.
OK, I'll let you know what happens...
select 'alter index '||index_name||' rebuild;' from dba_indexes where index_name like 'JC%';
and ran the resulting series of 77 "alter index ... rebuild" commands, all succeeded.
Unfortunately the structure searching still gives the same error, as attached.
Things somehow don't appear to add up here. JChem 5.3.3 already included the cache-id feature. JChem Cartridge 5.3.3 would throw the same error during search, if the CACHE_ID column was missing. Did you ever use JChem Cartridge 5.3.3 for searching?
Thanks for your patience Peter.
Yes, structure searching was fine at 5.3.3 - I trained somebody in IJC usage on Tuesday.
How difficult would it be to do a complete reinstall of JChem & Cartridge on the server?
Please, could you post the output of the following statement:
select jchem_core_pkg.getenvironment from dual;
Silly question, but...: Do you have multiple JChem Cartridge installations? Is there any chance that you connect to a different one with sqlplus (or whatever you use to execute bare-bones SQL commands) than with IJC?
We only have 1 cartridge installation, here is the select output:
SQL> select jchem_core_pkg.getenvironment from dual;
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_14
Java VM version: 14.0-b16
JChem version: 5.3.5
JChem Index version: 5030300
JDBC driver version: 220.127.116.11.0-Production
Off-line discussion and further analysis led to the current "working hypothesis" that materialized views might not behave as expected during upgrade and/or index rebuild. We are checking this hypothesis. (FS#11477)
I have tested the upgrade both from 5.3.1 to 5.3.6 and from 5.3.3 to 5.3.6 using a simple materialized view. Both upgrade appeared to go with the view as expected (without problem).
I suspect that the problem might be more due to (a) inconsistent entries in the JChemProperties table unrelated to materialized views and (b) JChem current lack of robustness in face of such inconsistencies -- than the upgrade strictly taken.