Create domain index error "index already created"

Hello,

I've already created the index but still showing me this error:

SELECT cd_id FROM kiet.sub_structure WHERE jc_compare(cd_structure, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C','t:s') = 1


Rapport d'erreur :


Erreur SQL : ORA-20101: Please, create domain index on the column referenced in the operator JC_COMPARE of the table with DATA_OBJECT_ID=61623. You can find out the name of the table by executing:


SELECT owner, object_name INTO schema_name, table_name


FROM sys.dba_objects WHERE DATA_OBJECT_ID = 61623


ORA-06512: à "KIET.JCHEM_CORE_PKG", ligne 131


ORA-06512: à "KIET.COMPARE_FUNCBV", ligne 10

My index creation sql:

  CREATE INDEX "KIET"."JC_IDX" ON "KIET"."SUB_STRUCTURE" ("CD_STRUCTURE") 


   INDEXTYPE IS "KIET"."JC_IDXTYPE" ;

SELECT cd_id FROM kiet.sub_structure WHERE jc_compare(cd_structure, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C','t:s') = 1;

Hello,

Did you install JChem Cartrridge in the KIET schema? (We recommend against putting user data in the same schema as JChem Cartridge.)

Peter

Yes, for example using kiet user on kiet schema:

select jc_compare('c1ccccc1', 'Brc1ccccc1', 't:r') from dual;


JC_COMPARE('C1CCCCC1','BRC1CCCCC1','T:R') 
----------------------------------------- 
1                                         
1 rows selected

FYI, this example doesn't demonstrate that JChem Cartridge is installed in the KIET schema. Typically, the JChem Catridge installer (or your DBA) will create public synonyms for the JChem Cartridge operators and packages. As a result whereever in the Oracle instance you call JC_COMPARE it will be resolved (to the operator pointed to by the public synonym), even if you don't prefix the operator with the containing schema.

Please, could post the output of the following command executed as KIET:

select jchem_core_pkg.getenvironment from dual;

?

Thanks

Peter

select jchem_core_pkg.getenvironment from dual;

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 Linux: Version 11.2.0.3.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

NLSRTL Version 11.2.0.3.0 - Production                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

JChem owner: KIET                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

JChem Server environment:                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

Java VM vendor: Oracle Corporation                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

Java version: 1.7.0_19                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

Java VM version: 23.7-b01                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

JChem version: 5.12.3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

JChem Index version: 5120000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     

JDBC driver version: 11.1.0.7.0-Production 

Yah sorry that was my stupidity talking :D, I was supposed to copy setpassword command , but it's installed on KIET and I already did try that command ;-)  ,I've droped the index then created it again and the error is no longer there, but I want to try it on my structure table and doesn't give me the results I want, for example:

This is one structure exported using Jchem manager:

  Mrv0541 05211312252D          

 18 19  0  0  0  0            999 V2000

    3.5954   -4.2428    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    3.5942   -5.0669    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    4.3138   -5.4802    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    4.3120   -3.8298    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.0281   -4.2434    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.0246   -5.0664    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.8128   -5.3218    0.0000 N   0  0  0  0  0  0  0  0  0  0  0  0

    6.2954   -4.6580    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    5.8092   -3.9875    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    6.0656   -3.2002    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    6.8706   -3.0248    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    7.1248   -2.2363    0.0000 N   0  0  0  0  0  0  0  0  0  0  0  0

    7.9297   -2.0610    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    8.1840   -1.2769    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    2.8711   -3.8302    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    2.1579   -4.2474    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    8.4874   -2.6746    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    7.1210   -4.6643    0.0000 I   0  0  0  0  0  0  0  0  0  0  0  0

  8  9  2  0  0  0  0

  9  5  1  0  0  0  0

  4  1  1  0  0  0  0

  9 10  1  0  0  0  0

  5  6  1  0  0  0  0

 10 11  1  0  0  0  0

 11 12  1  0  0  0  0

  2  3  1  0  0  0  0

 12 13  1  0  0  0  0

  3  6  2  0  0  0  0

 13 14  1  0  0  0  0

  1  2  2  0  0  0  0

  1 15  1  0  0  0  0

  5  4  2  0  0  0  0

 15 16  1  0  0  0  0

  6  7  1  0  0  0  0

 13 17  2  0  0  0  0

  7  8  1  0  0  0  0

  8 18  1  0  0  0  0

M  END

>  <CD_ID>

1

>  <CD_STRUCT_ID>

AB-1

>  <CD_SMILES>

COC1=CC=C2NC(I)=C(CCNC(C)=O)C2=C1 |c:17,t:2,4,8|

>  <CD_FORMULA>

C13H15IN2O2

>  <CD_SORTABLE_FORMULA>

C00013H00015I00001N00002O00002

>  <CD_MOLWEIGHT>

358.1749

>  <CD_HASH>

2137063866

>  <CD_TIMESTAMP>

2013-05-15 10:07:04.0

>  <CD_PRE_CALCULATED>

0

>  <CD_FP1>

-2049129400

>  <CD_FP2>

-1975910142

>  <CD_FP3>

-314273640

>  <CD_FP4>

-804510868

>  <CD_FP5>

143918090

>  <CD_FP6>

-2136889152

>  <CD_FP7>

436881942

>  <CD_FP8>

23610498

>  <CD_FP9>

127078662

>  <CD_FP10>

-190717804

>  <CD_FP11>

-603440896

>  <CD_FP12>

271842010

>  <CD_FP13>

152171312

>  <CD_FP14>

553089

>  <CD_FP15>

149802000

>  <CD_FP16>

-1892572016

And now when trying to create the index with this command:

  CREATE INDEX "KIET"."JC_IDX" ON "KIET"."SUB_STRUCTURE" ("CD_STRUCTURE") 

   INDEXTYPE IS "KIET"."JC_IDXTYPE" PARAMETERS('idxSubType=structures, structureType=molecules') ;

I'm getting 

ORA-20105: Illegal option: structureType
ORA-06512: à "KIET.JCHEM_CORE_PKG", ligne 45
ORA-06512: à "KIET.JC_IDXTYPE_IM", ligne 29
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.

Don't worry about the question before the last one cause I was using wrong string for the request:

instead of : COC1=CC=C(C(CC#N)=N1)[N+]([O-])=O ,I was using: COC1=CC=C2NC(I)=C(CCNC(C)=O)C2=C1 |c:17,t:2,4,8|

CREATE INDEX "KIET"."JC_IDX" ON "KIET"."SUB_STRUCTURE" ("CD_STRUCTURE") 
   INDEXTYPE IS "KIET"."JC_IDXTYPE" PARAMETERS('idxSubType=structures, structureType=molecules') ;

Index parameters are separated by commas only. Space is significant, it is interpreted as part of the parameter name or the parameter value.

Peter

CREATE INDEX "KIET"."JC_IDX" ON "KIET"."SUB_STRUCTURE" ("CD_STRUCTURE") 
   INDEXTYPE IS "KIET"."JC_IDXTYPE" PARAMETERS('idxSubType=structures, structureType=molecules') ;

Thank you very much Peter, cheers ;)

CREATE INDEX "KIET"."JC_IDX" ON "KIET"."SUB_STRUCTURE" ("CD_STRUCTURE") 
   INDEXTYPE IS "KIET"."JC_IDXTYPE" PARAMETERS('idxSubType=structures, structureType=molecules') ;

But still getting the same error message, I think if it's spacing problem only the name of the parameter will have a space on it; thanks.

ORA-20105: Illegal option:  structureType

There are two spaces between "option:" and "structureType". The first one is hard-coded to keep a space before the parameter name. The second space comes with the parameter name (you put after the comma).

Peter

ORA-20105: Illegal option:  structureType

Still getting the error message:

RA-20105: idxSubTypeis not supported with JChem structure tables

ORA-06512: à "KIET.JCHEM_CORE_PKG", ligne 45

ORA-06512: à "KIET.JC_IDXTYPE_IM", ligne 29

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.

Don't specify idxSubType.

Peter

that was the first thing that I've tried but still getting the error from ODCIINDEXCREATE 

Erreur SQL : ORA-29855: erreur d'exécution de la routine ODCIINDEXCREATE

ORA-29532: appel Java arrêté par une exception Java non interceptée : java.lang.Exception: In the case of JChem structure tables, structure type should be specified during table creation.

ORA-06512: à "KIET.JCHEM_CORE_PKG", ligne 105

ORA-06512: à "KIET.JC_IDXTYPE_IM", ligne 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.

Indeed, with JChem Base tables, the only index parameter that can be specified is the "JChemPropertiesTable".

Peter

Ok thank you very much peter ;-)