When trying to build an index, you might get a warning:ORA-29863: warning in the execution of ODCIINDEXCREATE routine

When trying to build an index, you might get a warning:

ORA-29863: warning in the execution of ODCIINDEXCREATE routine

This generally means that the cartridge worked as expected. It created a perfectly good index on your data. It is just warning you that one or more of the documents could not be indexed, probably because they contain bad or null data. If an exception is thrown by Oracle and not by the Cartridge, the index is marked by Oracle as LOADING/FAILED/UNUSABLE, and the only command a user can execute regarding the index is the ALTER INDEX REBUILD command.

You can find the offending records in the CSCartridge.INDEX_NAME_E table errors table for the unhandled records which are logged by the Cartridge. The non-interpreted records should be manually reviewed and corrected.

See the cartridge user’s guide for full details.


EXAMPLE:

When indexing the chemical structures in E-Notebook you will often get a warning message:


CREATE INDEX SX on ELN_CHEMICAL_STRUCTURES(CDXML) INDEXTYPE IS CsCartridge.MoleculeIndexType


ERROR at line 1:

ORA-29863: warning in the execution of ODCIINDEXCREATE routine


This does not mean the indexing has failed. The error is just a Cartridge exception which indicates that some records could not be indexed. This is usually because the records has no chemistry or the chemistry cannot be analyzed.


To investigate the cause of this you need to know

a) the number of records to be indexed

b) the number of records indexed

c) the number of records which gave an error


a) = b) + c)


The following is an example. Replace "cs_notebook9" with the name of the Enotebook schema owner


The total number of records in the ELN structure table:

select count(*) from cs_notebook9.eln_chemical_structures

82


The number of indexed records:

select count(*) from cscartridge.cs_notebook9_sx

60


The number of non-indexed records

select count(*) from cscartridge.cs_notebook9_sx_e

22


60 + 22 = 82 so all records are accounted for.


Looking in the cscartridge.cs_notebook9_sx_e table gives the internal rowid of the records which cannot be indexed. This is not very helpful
for manual inspection. The following query shows in a more friendly format the records which could not be indexed


SELECT e.primary_key, c.text
FROM cs_notebook9.eln_chemical_structures e, cscartridge.cs_notebook9_sx_e c
WHERE e.ROWID = c.rid
ORDER BY primary_key


PRIMARY_KEY    TEXT

1    Thu Aug 30 20:31:31 2007 zero molweight data

2    Thu Aug 30 20:31:31 2007 zero molweight data

3    Thu Aug 30 20:31:31 2007 zero molweight data

4    Thu Aug 30 20:31:31 2007 zero molweight data

5    Thu Aug 30 20:31:31 2007 empty record

6    Thu Aug 30 20:31:31 2007 empty record

60    Thu Aug 30 20:31:31 2007 zero molweight data

61    Thu Aug 30 20:31:31 2007 empty record

62    Thu Aug 30 20:31:31 2007 zero molweight data

63    Thu Aug 30 20:31:31 2007 empty record

64    Thu Aug 30 20:31:31 2007 empty record

65    Thu Aug 30 20:31:31 2007 empty record

66    Thu Aug 30 20:31:31 2007 empty record

67    Thu Aug 30 20:31:31 2007 empty record

68    Thu Aug 30 20:31:31 2007 empty record

69    Thu Aug 30 20:31:31 2007 empty record

70    Thu Aug 30 20:31:31 2007 empty record

71    Thu Aug 30 20:31:31 2007 empty record

72    Thu Aug 30 20:31:31 2007 zero molweight data

73    Thu Aug 30 20:31:31 2007 zero molweight data

81    Thu Aug 30 20:31:31 2007 zero molweight data

104    Thu Aug 30 20:31:31 2007 zero molweight data


Opening some of these records in ChemDraw show that they are blank or contain no chemistry.


You can confirm the index is functional by searching and adding new structures to the database using the ELN application and then checking the indexes have been updated.