error inserting a structure

Hi,

We have 2 jchem computational server, 1 production & 1 development.

the same insert statement works fine in production but gives the following error in development.

jchem_table_pkg.jc_insert( JCMV53.jcf_standardizeb(STRUCTURE,'sep=! config:dearomatize',NULL), tablename, NULL, 'true' ,'false', NULL);

ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Statement 'INSERT INTO CHEMDB.SALT (cd_id, cd_structure, cd_smiles, cd_formula, cd_molweight, cd_hash, cd_flags, cd_timestamp, cd_fp1, cd_fp2, cd_fp3, cd_fp4, cd_fp5, cd_fp6, cd_fp7, cd_fp8, cd_fp9, cd_fp10, cd_fp11, cd_fp12, cd_fp13, cd_fp14, cd_fp15, cd_fp16)  VALUES(?, empty_blob(), ?, ?, ?, ?, ?, SYSDATE, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' failed for structure '
  Marvin  07311315252D          

 10  9  0

In the development server, the following executes fine.

select jchem_core_pkg.getenvironment() from dual;

Oracle environment:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_20
Java VM version: 16.3-b01
JChem version: 5.2.6
JChem Index version: 5020400
JDBC driver version: 11.1.0.7.0-Production

 Please help!

Thanks!

Regards,

Samantha

Hi Samantha,

Please, could you post (or send to us) the corresponding Java error & stack trace from the Oracle session trace file? (Session trace files are located in the directory returned by the show parameter user_dump_dest command.)

Regards,

Peter 

Hi,

See attached trace file.

Cheers,

Samantha

Thank you, Samantha!

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Please, try recreating the JChem index on the table.

Let us know if this doesn't help.

Peter

Hi Peter,

It works! Thanks @__@

Any idea why the domain index suddenly rendered unusable? How do we prevent that in the future?

THanks.

Regards,
Samantha

Hi Samantha,

Glad that it works.

The index typically gets in INVALID/etc. state, when CREATE INDEX or ALTER INDEX is aborted for some reason. Indexes (built-in or JChem) are not supposed get invalidated during normal operation.

Peter