Issue creating index on PRODUCTION instance table

Hello

I am having an issue creating a jchem.jc_idxtype on a newly created Structure table in our oracle instance.  

The table is called "STRUCTURE" and has the following attributes (was not created using JChemBase):

ID NUMBER(19,0) No

VERSION NUMBER(19,0) No

CDID NUMBER(10,0) Yes

PSA FLOAT Yes

SMILES VARCHAR2(4000 CHAR) Yes

STRUCTURE LONG RAW Yes

When I run:

create index smiles_indx on structure (smiles) INDEXTYPE IS jchem.jc_idxtype

I receive the following error message:

Error starting at line 47 in command:

create index smiles_indx on structure (smiles) INDEXTYPE IS jchem.jc_idxtype

Error at Command Line:47 Column:14

Error report:

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

ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-00904: "CD_STRUCTURE": invalid identifier

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

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

29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"

*Cause:    Failed to successfully execute the ODCIIndexCreate routine.

*Action:   Check to see if the routine has been coded correctly.

 

I was able to create an index on another table in the same schema without issue.  I am not sure why "ORA-00904: "CD_STRUCTURE": invalid identifier"  is the error as that column does not exist in the STRUCTURE table itself.  Is the issue with the name of the table?

Thank you 

Dennis

 

Hi Dennis,

Could you send us the log files (jcart<n>.log and trace<n>.log) generated by the error. (log files are available in jchem\cartridge\logs\ )

Thank you,

Krisztina

Hi Krisztina-

Where should I send the logs or do you want me to zip them and attach?

Dennis

You can send them to my email address kvajda at chemaxon dot com. Please write us which JCC version are you using.

thanks

Krisztina

I am attaching a zipped folder of all the logs, thank you Kirsztina

Dennis

Thanks for the logs.

Would you send the output of

select jchem_core_pkg.getenvironment from dual;

thank you,

Krisztina

I emailed them to you as well, but here they are:

 

"Oracle environment: 

Oracle Database 11g 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 64-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

 

JChem owner: JCHEM

 

JChem Server environment: 

Java VM vendor: Oracle Corporation

Java version: 1.7.0_21

Java VM version: 23.21-b01

JChem version: 6.0.5

JChem Index version: 6000000

JDBC driver version: 11.1.0.7.0-Production

"

Hi Dennis,

Our first suggestion is to drop index and create it again:

drop index smiles_indx;
create index smiles_indx on structure (smiles) INDEXTYPE IS jchem.jc_idxtype;

Krisztina

Yeah I tried that, the index drops but still the same error upon creation.  

Could you send us in email the content of the respective JCHEMPROPERTIES table (it doesn't contain any confidential data) ?

Thank you,

Krisztina

Just to close the thread: there was an old JChem table which was dropped by sql statement and not by drop_jctable. This caused that some unnecessary rows remained in the jchemproperties table for the old table, they had to be removed manually.

Tamas