ChemAxon Forum Archive » Storage & search: JChem Base and JChem Oracle Cartridge

ORA-29855 when trying to create JChem Index

User f698d0529d

06-05-2005 16:24:02

Hi - I’ve got a problem, and I am out of ideas and would like some help.


We have successfully used your Jchem cartridge before here at EvotecOAI, but I can’t get it to work at the moment. Thanks - Mark Reddin.





Red Hat Enterprise Linux AS release 3 (Taroon)


Kernel 2.4.21-4.ELsmp on an i686





Oracle version 10.1.0





Apache Tomcat/4.1.30





JChem version: 3.0.8


JChem Streams version: 3.0.8





Test.sh seems to work when passing the credentials of the user.





The problem is that when I try to create an index on a SMILES field of a table, it fails..





CREATE INDEX JC_IDX_V_SMILES ON VERSION


(ISOSMILES)


INDEXTYPE IS JCHEM.JC_IDXTYPE


PARAMETERS('TABLESPACE=indx_eoaiacd_V15');





The error which comes back varies between these two





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


ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.net.SocketPermission uksap12 resolve) has not been granted to EOAIACD_V15. The PL/SQL to grant this is dbms_java.grant_permission( 'EOAIACD_V15', 'SYS:java.net.SocketPermission', 'uksap12', 'resolve' )


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


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





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


ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: The following exception has been thrown by the servlet:


Exception: ORA-01031: insufficient privileges


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


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





I try what it suggests, connected as sys, namely





call dbms_java.grant_permission( 'EOAIACD_V15', 'SYS:java.net.SocketPermission', 'uksap12', 'resolve' );





Although it reports back saying “method called”, it does not make any difference





Tomcat is running on port 8089, so I have also tried replacing ‘uksap12’ with ‘serverip:8089’, ‘servername:8089’, ‘localhost:8089’ and ‘127.0.0.1:8089’ to no avail.





Something simple seems to be amiss, because I can connect as the Jchem user, and create a table with a smiles field, and index this with no problems.





In terms of what I granted to the user, I did this (I pulled it out with the following SQL)





select 'grant execute on ' || a.OBJECT_NAME || ' to eoaiacd_v15;' from all_objects a where lower(a.OWNER) = 'jchem' and a.OBJECT_TYPE in


('PACKAGE', 'FUNCTION', 'OPERATOR', 'TYPE', 'INDEXTYPE', 'PROCEDURE');





grant execute on JCHEM_REFCUR_PKG to eoaiacd_v15;


grant execute on JCHEM_CORE_PKG to eoaiacd_v15;


grant execute on JCHEM_MISC_PKG to eoaiacd_v15;


grant execute on RESARRAY to eoaiacd_v15;


grant execute on CONTAINS_FUNC to eoaiacd_v15;


grant execute on EQUALS_FUNC to eoaiacd_v15;


grant execute on EVALUATE_FUNC to eoaiacd_v15;


grant execute on COMPARE_FUNC to eoaiacd_v15;


grant execute on MATCHCOUNT_FUNC to eoaiacd_v15;


grant execute on TANIMOTO_FUNC to eoaiacd_v15;


grant execute on DISSIMILARITY_FUNC to eoaiacd_v15;


grant execute on LOGP_FUNC to eoaiacd_v15;


grant execute on LOGD_FUNC to eoaiacd_v15;


grant execute on PKA_FUNC to eoaiacd_v15;


grant execute on TPSA_FUNC to eoaiacd_v15;


grant execute on MOLWEIGHT_FUNC to eoaiacd_v15;


grant execute on FORMULA_FUNC to eoaiacd_v15;


grant execute on MOLCONVERT_FUNC to eoaiacd_v15;


grant execute on MOLCONVERTB_FUNC to eoaiacd_v15;


grant execute on TRANSFORM_FUNC to eoaiacd_v15;


grant execute on REACT_FUNC to eoaiacd_v15;


grant execute on FORMULA_FUNC_EQ to eoaiacd_v15;


grant execute on JC_INSERT to eoaiacd_v15;


grant execute on JC_UPDATE to eoaiacd_v15;


grant execute on JC_DELETE to eoaiacd_v15;


grant execute on JC_SET_DEFAULT_PROPERTY to eoaiacd_v15;


grant execute on USER_DEF_FUNC to eoaiacd_v15;


grant execute on CD_ID_ARRAY to eoaiacd_v15;


grant execute on RIDARRAY to eoaiacd_v15;


grant execute on MOLPROPS_ARRAY to eoaiacd_v15;


grant execute on MOLPROPS_ARRAY_ARRAY to eoaiacd_v15;


grant execute on JC_IDXTYPE_IM to eoaiacd_v15;


grant execute on JC_CONTAINS to eoaiacd_v15;


grant execute on JC_EQUALS to eoaiacd_v15;


grant execute on JC_COMPARE to eoaiacd_v15;


grant execute on JC_EVALUATE to eoaiacd_v15;


grant execute on JC_MATCHCOUNT to eoaiacd_v15;


grant execute on JC_TANIMOTO to eoaiacd_v15;


grant execute on JC_DISSIMILARITY to eoaiacd_v15;


grant execute on JC_LOGP to eoaiacd_v15;


grant execute on JC_LOGD to eoaiacd_v15;


grant execute on JC_PKA to eoaiacd_v15;


grant execute on JC_TPSA to eoaiacd_v15;


grant execute on JCF_CONTAINS to eoaiacd_v15;


grant execute on JCF_EQUALS to eoaiacd_v15;


grant execute on JCF_EVALUATE to eoaiacd_v15;


grant execute on JCF_COMPARE to eoaiacd_v15;


grant execute on JCF_MATCHCOUNT to eoaiacd_v15;


grant execute on JCF_TANIMOTO to eoaiacd_v15;


grant execute on JCF_DISSIMILARITY to eoaiacd_v15;


grant execute on JCF_MOLWEIGHT to eoaiacd_v15;


grant execute on JCF_FORMULA to eoaiacd_v15;


grant execute on JCF_MOLCONVERT to eoaiacd_v15;


grant execute on JCF_MOLCONVERTB to eoaiacd_v15;


grant execute on JCF_TRANSFORM to eoaiacd_v15;


grant execute on JCF_REACT to eoaiacd_v15;


grant execute on JCF_FORMULA_EQ to eoaiacd_v15;


grant execute on JC_MOLWEIGHT to eoaiacd_v15;


grant execute on JC_FORMULA to eoaiacd_v15;


grant execute on JC_MOLCONVERT to eoaiacd_v15;


grant execute on JC_MOLCONVERTB to eoaiacd_v15;


grant execute on JC_FORMULA_EQ to eoaiacd_v15;


grant execute on JC_REACT to eoaiacd_v15;


grant execute on JC_TRANSFORM to eoaiacd_v15;


grant execute on JC_IDXTYPE to eoaiacd_v15;





and I used a similar script to create local synonyms for Jchem objects in the eoaiacd_v15 schema.

ChemAxon aa7c50abf8

06-05-2005 17:33:54

Mark,
Quote:



The error which comes back varies between these two





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


ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission (java.net.SocketPermission uksap12 resolve) has not been granted to EOAIACD_V15. The PL/SQL to grant this is dbms_java.grant_permission( 'EOAIACD_V15', 'SYS:java.net.SocketPermission', 'uksap12', 'resolve' )


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


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





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


ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: The following exception has been thrown by the servlet:


Exception: ORA-01031: insufficient privileges


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


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 16
I find it difficult to explain the fact that the resolve error and the the Tomcat problem are alternating. If Oracle prevents you from resolving the IP of the Tomcat host (the first error message), it surely will not allow you to connect to it, hence it is impossible for your database session to get any error message from Tomcat (the second error message with ORA-01031).





I suggest to first clarify the SocketPermission problem (with the 'resolve' issue). We have to eliminate that one before addressing the Tomcat error message with ORA-01031.
Quote:
I try what it suggests, connected as sys, namely





call dbms_java.grant_permission( 'EOAIACD_V15', 'SYS:java.net.SocketPermission', 'uksap12', 'resolve' );





Although it reports back saying “method called”, it does not make any difference
Please, double check if you really got the same error message again. Let's assume you successfully granted 'resolve' permission. Upon the next trial, a very similar error message appears complaining about 'connect' permission instead of 'resolve'. If you really get the same error message again (and the granting call seems to complete successfully), then chances are that you logged into SQL Plus for the index creation as a user other than 'EOAIACD_V15' or you connected to a database instance other than where you granted the 'resolve' SocketPermission. The fact that the test.sh script completes successfully points to a similar direction: you may be confusing either the users or the database instances.





Peter

User f698d0529d

09-05-2005 09:10:06

The error message I get from JChem does not alternate - it just varies from time to time. Mostly, it says nothing other than insufficient priviledges.





Please see transcript run from sqlplus below. I think it proves that I am not getting my connections mixed up. Also, at the end, please note that it proves that the JChem user can create a JChem index, no problem.


Thanks for your help.





C:\Documents and Settings\mreddin>sqlplus eoaiacd_v15@evoseek_dev





SQL*Plus: Release 10.1.0.3.0 - Production on Mon May 9 09:50:23 2005





Copyright (c) 1982, 2004, Oracle. All rights reserved.





Enter password:





Connected to:


Oracle Database 10g Release 10.1.0.3.0 - Production





SQL> select user from dual;





USER


------------------------------


EOAIACD_V15





SQL> select name from v$database;





NAME


---------


EVSKDEV





SQL> CREATE INDEX JC_IDX_V_SMILES ON VERSION


2 (ISOSMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=indx_eoaiacd_V15');


CREATE INDEX JC_IDX_V_SMILES ON VERSION


*


ERROR at line 1:


ORA-00955: name is already used by an existing object








SQL> drop index JC_IDX_V_SMILES;





Index dropped.





SQL> CREATE INDEX JC_IDX_V_SMILES ON VERSION


2 (ISOSMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=indx_eoaiacd_V15');


CREATE INDEX JC_IDX_V_SMILES ON VERSION


*


ERROR at line 1:


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


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: ORA-01031: insufficient privileges


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


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








SQL> connect sys@evoseek_dev as sysdba


Enter password:


Connected.


SQL> select user from dual;





USER


------------------------------


SYS





SQL> select name from v$database;





NAME


---------


EVSKDEV





SQL> call dbms_java.grant_permission( 'EOAIACD_V15', 'SYS:java.net.SocketPermission', 'uksap12,uksap12:8089,127.0.0.1,127.0.0.1:8089,localhost,localhost:8089,10.1.48.27,10.1.48.27:8089', 'connect,resolve' );





Call completed.





SQL> connect eoaiacd_v15@evoseek_dev


Enter password:


Connected.


SQL> select user from dual;





USER


------------------------------


EOAIACD_V15





SQL> select name from v$database;





NAME


---------


EVSKDEV





SQL> CREATE INDEX JC_IDX_V_SMILES ON VERSION


2 (ISOSMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=indx_eoaiacd_V15');


CREATE INDEX JC_IDX_V_SMILES ON VERSION


*


ERROR at line 1:


ORA-00955: name is already used by an existing object








SQL> drop index JC_IDX_V_SMILES;





Index dropped.





SQL> CREATE INDEX JC_IDX_V_SMILES ON VERSION


2 (ISOSMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=indx_eoaiacd_V15');


CREATE INDEX JC_IDX_V_SMILES ON VERSION


*


ERROR at line 1:


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


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: ORA-01031: insufficient privileges


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


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








SQL> connect jchem@evoseek_dev


Enter password:


Connected.


SQL> select user from dual;





USER


------------------------------


JCHEM





SQL> select name from v$database;





NAME


---------


EVSKDEV





SQL> CREATE INDEX JC_IDX_V_SMILES ON TEST_MCR


2 (SMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=data_jchem');


CREATE INDEX JC_IDX_V_SMILES ON TEST_MCR


*


ERROR at line 1:


ORA-00955: name is already used by an existing object








SQL> drop index jc_idx_v_smiles;





Index dropped.





SQL> CREATE INDEX JC_IDX_V_SMILES ON TEST_MCR


2 (SMILES)


3 INDEXTYPE IS JCHEM.JC_IDXTYPE


4 PARAMETERS('TABLESPACE=data_jchem');





Index created.





SQL>

ChemAxon aa7c50abf8

09-05-2005 10:13:49

Please, make sure that (in addition to the privileges listed in your first posting) the following privileges are also granted to the user configured for the JDBC connection in Tomcat (which is probably 'jchem' in your case):





grant SELECT ANY TABLE to $jchemowner;


grant SELECT ANY SEQUENCE to $jchemowner;


grant DELETE ANY TABLE to $jchemowner;


grant CREATE ANY TABLE to $jchemowner;


grant CREATE ANY INDEX to $jchemowner;


grant CREATE ANY SEQUENCE to $jchemowner;


grant CREATE ANY TRIGGER to $jchemowner;


grant INSERT ANY TABLE to $jchemowner;


grant UPDATE ANY TABLE to $jchemowner;


grant DROP ANY TABLE to $jchemowner;


grant DROP ANY SEQUENCE to $jchemowner;


grant DROP ANY TRIGGER to $jchemowner;





Please, see http://www.jchem.com/doc/admin/cartridge.html#server_jdbc for details.





You may also be interested in looking at http://www.chemaxon.hu/forum/ftopic425.html.





Please, note that you need to execute the "dbms_java.grant_permission" procedures only once for each user (with 'resolve,connect').

User f698d0529d

09-05-2005 13:38:51

Thank you for that information. It seems to have resolved the immediate problem I was having.





I am having new problems now, but if I cannot resolve them, I will start a new thread.





Thanks again


Mark