cartridge index creation error

Hi,

I have installed the JChem server, and server is up and running without probem. I have also run a couple of recommended diagnostic checking on the JChem server it seems working fine. However when I try to create a cartridge index I am getting following error:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException: oracle.jdbc.driver.OracleSQLException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA": invalid identifier
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 101
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 20

As you know the installation process has to invovle DBA where he run the SQL to create jchem, and grant privileges. I think DBA and I have done everything right based on the book. Wondering what we need to check to fix the problem.

I did another installation a while ago and it goes pretty smooth, this time I am pretty much follow the same routine.

Thanks

Dong

To help you diagnose, I noticed that there is another crucial error signaling things were not right.

When I do call jchem.jchem_core_pkg.use_password('passwd')

I am getting following error:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 66
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 57

I am using jchem 5.9.

When I do

select * from user_role_privs

I am getting following, user metabolite is intended cartridge index owner

As jchem run statement:

select

Oracle environment:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

JChem owner: JCHEM

JChem Server environment:

Java VM vendor: Sun Microsystems Inc.

Java version: 1.6.0_27

Java VM version: 20.2-b06

JChem version: 5.9.1

JChem Index version: 5080100

JDBC driver version: 11.1.0.7.0-Production

Hi Dong,

Please, could you send us the Java stack trace from the Oracle session trace corresponding to this problem ("NullPointerException")

Thanks

Peter

Hi Peter,

I have attached the log file jcart1.log. Let me know if this is what you need

Thanks

Dong

Hi Dong,

I would need the corresponding session trace file from the Oracle server: in my environment it is in the $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace directory.

Thanks

Peter

I have to ask DBA to get the trace file. Attached trace file is dated as April 15th. The date when I try to do the index again. I do not know if yo can find any clue for the problem from this trace file. It was not easy to get the file from DBA.

Thanks

Dong

Hi Dong,

This is not the trace which can help us. The one which we would need contains the text NullPointerException.

Peter

This is what DBA sent me in  $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace directory. I do not know what's in the directory since the dir is owned by Oracle.

We plan to add a feature to JChem Cartridge which will consist of logging errors generated in Oracle Java Stored Procedures in the JChem Cartridge server's log directory as well for easier access by Application Administrators.

Hi Peter,

I dig out following from trace dump from DBA, and found following error in trace when I issued call jchem.jchem_core_pkg.use_password('metabolite1') or try to create index. I think if we can figure out the cause for one problem we shoul be all set.

Towards the end it says

No synonym for any JCHEM_CORE_PKG

Exception in thread "Root Thread" java.lang.NullPointerException

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.locate(RmiDirectory.java:95)

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.getServer(RmiDirectory.java:174)

at chemaxon.jchem.cartridge.JFunctions.getRmiTunnel(JFunctions.java:386)

at chemaxon.jchem.cartridge.JFunctions.usePassword(JFunctions.java:361)

Trace cooresponding to the error

Trace file /u01/home/oracle/diag/rdbms/metap/METAP/trace/METAP_ora_13537.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/home/oracle/product/11.2.0

System name: Linux

Node name: hpwbls005

Release: 2.6.18-164.el5

Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009

Machine: x86_64

Instance name: METAP

Redo thread mounted by this instance: 1

Oracle process number: 36

Unix process pid: 13537, image: oracle@hpwbls005

*** 2012-04-14 07:43:59.794

*** SESSION ID:(398.8735) 2012-04-14 07:43:59.794

*** CLIENT ID:() 2012-04-14 07:43:59.794

*** SERVICE NAME:(SYS$USERS) 2012-04-14 07:43:59.794

*** MODULE NAME:(TOAD 9.6.1.1) 2012-04-14 07:43:59.794

*** ACTION NAME:() 2012-04-14 07:43:59.794

oracle.jdbc.driver.OracleSQLException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA": invalid identifier

at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)

at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java:105)

at oracle.jdbc.driver.T2SPreparedStatement.describe_internal(T2SPreparedStatement.java)

at oracle.jdbc.driver.T2SPreparedStatement.executeForDescribe(T2SPreparedStatement.java)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)

at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java)

at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java)

at chemaxon.jchem.cartridge.dbsession.DbSessionBase.getJccOwner(DbSessionBase.java:94)

at chemaxon.jchem.cartridge.dbsession.JavaStoredProcSession.instance(JavaStoredProcSession.java:353)

at chemaxon.jchem.cartridge.dbsession.JavaStoredProcSession.instance(JavaStoredProcSession.java:346)

at chemaxon.jchem.cartridge.JFunctions.usePassword(JFunctions.java:337)

[METABOLITE:METABOLITE]: Failing sql: select jchem_core_pkg.get_cartowner_schema() from dual

user=METABOLITE

No privilege on any JCHEM_CORE_PKG

*** 2012-04-14 07:44:00.300

No synonym for any JCHEM_CORE_PKG

Exception in thread "Root Thread" java.lang.RuntimeException: oracle.jdbc.driver.OracleSQLException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA": invalid identifier

at chemaxon.jchem.cartridge.dbsession.DbSessionBase.getJccOwner(DbSessionBase.java:108)

at chemaxon.jchem.cartridge.dbsession.JavaStoredProcSession.instance(JavaStoredProcSession.java:353)

at chemaxon.jchem.cartridge.dbsession.JavaStoredProcSession.instance(JavaStoredProcSession.java:346)

at chemaxon.jchem.cartridge.JFunctions.usePassword(JFunctions.java:337)

Caused by: oracle.jdbc.driver.OracleSQLException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA": invalid identifier

at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)

at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java:105)

at oracle.jdbc.driver.T2SPreparedStatement.describe_internal(T2SPreparedStatement.java)

at oracle.jdbc.driver.T2SPreparedStatement.executeForDescribe(T2SPreparedStatement.java)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)

at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java)

at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java)

at chemaxon.jchem.cartridge.dbsession.DbSessionBase.getJccOwner(DbSessionBase.java:94)

... 3 more

*** 2012-04-14 07:55:37.412

2012-04-14 07:55:37.410 [881067, METABOLITE, METABOLITE]: WARNING - chemaxon.jchem.cartridge.JcMetaDataFunctions: Name of the connected Oracle instance could not be obtained.

oracle.jdbc.driver.OracleSQLException: ORA-00904: "JCHEM_CORE_PKG"."GET_CARTOWNER_SCHEMA": invalid identifier

at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)

at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java:105)

at oracle.jdbc.driver.T2SPreparedStatement.describe_internal(T2SPreparedStatement.java)

at oracle.jdbc.driver.T2SPreparedStatement.executeForDescribe(T2SPreparedStatement.java)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)

at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java)

at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java)

at chemaxon.jchem.cartridge.dbsession.DbSessionBase.getJccOwner(DbSessionBase.java:94)

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.getRmiServerDesc(RmiDirectory.java:225)

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.locateBypassCache(RmiDirectory.java:105)

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.locate(RmiDirectory.java:90)

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.getServer(RmiDirectory.java:174)

at chemaxon.jchem.cartridge.JFunctions.getRmiTunnel(JFunctions.java:386)

at chemaxon.jchem.cartridge.JFunctions.usePassword(JFunctions.java:361)

[METABOLITE:METABOLITE]: Failing sql: select jchem_core_pkg.get_cartowner_schema() from dual

user=METABOLITE

role=JCC_BASIC_ROLE, owner=JCHEM, table_name=JCHEM_CORE_PKG, privilege=EXECUTE

No synonym for any JCHEM_CORE_PKG

Exception in thread "Root Thread" java.lang.NullPointerException

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.locate(RmiDirectory.java:95)

at chemaxon.jchem.cartridge.rmi.client.RmiDirectory.getServer(RmiDirectory.java:174)

at chemaxon.jchem.cartridge.JFunctions.getRmiTunnel(JFunctions.java:386)

at chemaxon.jchem.cartridge.JFunctions.usePassword(JFunctions.java:361)

Hi Dong,

The problem is most probably that the public synonyms for the objects in the JChem Cartridge owner's schema have gotten lost along the way.

Please, try to recreate the public synonyms by executing:

call jchem.privman_pkg.public_syns_for_jcobjs('JCHEM');

The above command assumes that JChem Cartridge has been installed in the JCHEM schema. It may need to be executed by your DBA.

Alternatively, if, for example, your DBA is too busy to do it, JChem Cartridge users can create private synonyms by executing:

call jchem.privman_pkg.syns_for_jcobjs('JCHEM');

Users need CREATE SYNONYM privilege to create private synonyms. Starting with Oracle 11.2, this privilege is not granted by default to users.

Peter

OK as jchem I I ran call jchem.privman_pkg.syns_for_jcobjs('JCHEM') then

as JCC user metabolite I then ran call jchem.jchem_core_pkg.use_password('metabolite1')

I am getting error

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 66
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 57

whatelse should I try?

What do you get, when execute the following SQL as METABOLITE:

select jchem_core_pkg.get_cartowner_schema() from dual

?

Thanks

Peter

select jchem_core_pkg.get_cartowner_schema() from dual

I am getting JCHEM as metabolite, which is right.

Just so you know in metabolite schema symxy's Direct cartridge is also installed. I do not know if this has interfered with jchem cartridge

Do you experience the same problem using a fresh database connection?

tried many times shutting down the server, restart, get new db session. nothing seems help

And do you still have the same error messages in the Oracle session trace with the select jchem_core_pkg.get_cartowner_schema() from dual statement failing?

Thanks

Peter

correct

I can't see any way forward other than having a closer look to your environment at a web-conference (gotomeeting or webex). If this is OK with you, drop me a mail (pkovacs at chemaxon dot com) to let me know who should organize it (you or me).

Peter

Hi Pete,

You are right, the DBA has to do :

call jchem.privman_pkg.public_syns_for_jcobjs('jchem');

Actualluy our DBA did remind me he should do this early on in the installation step. I should have listen to him

Thanks

Dong