Issue creating jc_indextype after cartridge install

Hello

I am running into an issue when trying to create an index on a structure table using the cartridge.   We are migrating our oracle instance, and I have done the following:

1. Installed Oracle and copied over existing schemas


2. Installed Jchem Cartridge and recreated the jchem schema in oracle during the install


3. Cheked the cartridge install as per "Testing the JChem Cartridge Installation" section of the Admin docs


4. granted my "Compound" user the JCC_BASIC_ROLE

With the user compound I tried the following index creation:

create index jc_idx on compounds_22956690 (cd_smiles) INDEXTYPE IS jchem.jc_idxtype

I got the following error message 

Error starting at line 5 in command:

create index jc_idx on compounds_22955250 (cd_smiles) INDEXTYPE IS jchem.jc_idxtype

Error at Command Line:5 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: java.lang.NoClassDefFoundError

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.

 

The index does seem to be created, but is not useable if I try the following:

select structureid from compounds_22955250 where jchem.jc_compare(cd_smiles, ('CCC'),'t:s maxHitCount:5000')=1

I receive the following mesage:

ORA-20101: Please, create domain index on the column referenced in the operator JC_COMPARE of the table with DATA_OBJECT_ID=76245. 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 = 76245

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

ORA-06512: at "JCHEM.EXEC_FUNC", line 11

ORA-06512: at "JCHEM.COMPARE_FUNC", line 7

Not sure how to proceed here, I am assuming this may be an install isssue?

Thank you for your assistance

Hello,

Please, could you tell us the JChem version you are using?

(If the creation of a JChem index fails (with an error), the index is likely to be unusable...I am not sure why Oracle doesn't enter this failed index in the data dictionary marking it invalid. [It may be irrelevant for this issue, but in your post the substructure search is executed against a different table than the one with the failed index operation.])

Thank you for reporting this issue.

Peter

Peter,

Thanks for the quick reply.  I installed it today so it is whatever version is on the download page (sorry I am away from my desk)

Dennis

So it looks like version jchem-6.0.5

Let me know if you need anything else..

Dennis

Dennis,

I couldn't reproduce the indexing error so far.

Do you execute the CREATE INDEX SQL in a stored procedure or as a top-level SQL? If in a stored stored procedure, you will have to grant the privileges on the JCHEM objects directly to the COMPOUND user (as opposed to granting them through a role). (Execute

bash config-util.sh list-sqls-for-jcc-user-privs

in the jchem/cartridge directory.)

Peter

Peter

Thanks for looking into this.  So should I simply execute this SQL with DBA privs?  Or should I grant via the JCHEM user?

Thank you

Dennis

Dennis,

I get an output like this:

====> -------------------------- CUT HERE ----------------------------

====> -- To be executed as DBA:

====> call dbms_java.grant_permission('ZAZIE', 'SYS:java.net.SocketPermission', 'localhost:1099', 'resolve,connect');

====> 

====> -- To be executed as jchem:

====> call jchem.privman_pkg.grants_on_jcobjs('jchem', 'zazie');

====> -------------------------- CUT HERE ----------------------------

Maybe the CUT HERE thing is a bit misleading/confusing....?

Peter

Sorry, I asked the question before running the script to get the output.   I can run each set of scripts and I will let you know how it turns out.  Thank you for your help,

Dennis 

Peter

So I ran both commands (one under DBA and the other under jchem),

dba-call dbms_java.grant_permission('compound', 'SYS:java.net.SocketPermission', '

sage-util.corp.com:1099', 'resolve,connect');

jchem-call jchem.privman_pkg.grants_on_jcobjs('jchem', 'compound');

 and I am still getting the error below when trying to create an index on a structure table using the 'compound' user:

Error starting at line 1 in command:

create index jc_idx on compounds_22952370 (cd_smiles) INDEXTYPE IS jchem.jc_idxtype

Error at Command Line:1 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: java.lang.NoClassDefFoundError

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.

It is definitely an issue with permissions as I was able to create the index on the compound.compound_22955250 table using the jchem user and then execute a JC_COMPARE routine to fetch structures.

Is there another way to check whether I have the correct permissions?   Do I need to restart the JCHEM server after providing the permissions?

Thank you for your help.

Dennis

Dennis,

Yes, restarting the JChem Cartridge is usually helpful/needed, but in this case the problem already occurs in the "primary" database session (not in one opened by the JChem Cartridge server).

One potential problem which immediately meets my eye is that the user name in the following call is case-sensitive:

call dbms_java.grant_permission('compound', 'SYS:java.net.SocketPermission', 'sage-util.corp.com:1099', 'resolve,connect');

Unless you explicitely created the "compound" user in lower-case letter (CREATE USER "compound" ...), it should read:

dba-call dbms_java.grant_permission('COMPOUND', 'SYS:java.net.SocketPermission', 'sage-util.corp.com:1099', 'resolve,connect');

Peter

Peter

I re-ran the DBA/JChem Scripts making sure 'COMPOUND' was capitalized, and restarted the server.  Still no change. Should I try simply re-installing from the install script and writing over the JCHEM user?  

Any special permissions needed on the COMPOUND user?  Or should running the two commands as DBA/JCHEM be sufficient?

Dennis

Dennis,

I understand you re-installed JChem Cartridge after migrating Oracle...I am not sure what a newer installation could bring yet, but it is certainly an option, if all else fails, which point may be pretty close...

BTW, what does it mean Oracle was "migrated"? Was it moved from one host to another? The COMPOUND schema was also "copied over"? What does "copied over" mean?

Does COMPOUND perhaps have private (stale) synonyms? I have the hazy feeling that COMPOUND may be trying to use the wrong/non-existent JCHEM schema (objects).

If you re-installed JChem Cartridge after Oracle migration, new proper public synonyms should have created for the JCHEM schema.) Please, could you also check that the JCHEM_CORE_PKG public synonym appropriately points to the current JCHEM schema -- you could eventually drop the JCHEM_CORE_PKG public synonym and recreate it, just to be absolutely sure. As SYSTEM:

DROP PUBLIC SYNONYM jchem_core_pkg;

CREATE PUBLIC SYNONYM jchem_core_pkg FOR jchem.jchem_core_pkg;

If fiddling with the synonyms doesn't help, please, could you send me the output of the following SQL executed once as JCHEM and once as COMPOUND:

select jchem_core_pkg.getenvironment from dual;

?

Please, could you also send me one of the Oracle session trace logs containing the NoClassDefFoundError error message (there are presumably several of them)?

Thanks,

Peter

The SYNONYM drop and recreate did the trick, thanks for sticking with me Peter!!!  

Just for clarity in case someone else reads this thread for help I will explain the oracle migration.  We currently have a hosted oracle instance which we are going to migrate internally.  As such we have installed a fresh oracle db, and then we imported the JCHEM and COMPOUND schemas as is.  The JChem server is also hosted remotely, and I am bringing that internally as well.  This is where I think I created the issue.  I installed the jchem server on a new server, in doing such I thought it would be best to overwrite the JChem schema for some reason (clearly not a good idea).  I suspect when I did that (as you correclty identified) I left a stale synonym somewhere.  

Thank you again, still the best support in the industry!!  Let me know if you want me to include anything else in the thread for future considerations.

Dennis

Dennis,

I am glad if we could help you.

Thank you for the clarification!

As other synonyms may also have gone awry, consider running as SYSTEM the

privman_pkg.drop_public_syns_for_jcobjs(jchemowner varchar2); 

and the

privman_pkg.public_syns_for_jcobjs(jchemowner varchar2);