ORA-00903: invalid table name in 5.3.3 Cartridge install

Hi there,

We upgraded a couple of databases to 5.3.3 a couple of weeks ago and have been using them fine during that period.  The machines got rebooted over the weekend and now we are not able to do structure searches with the error being: ORA-00903: invalid table name

Any ideas?

Ian

Query:

select

environment:

* from compound_list where jc_compare(smiles, 'ClC1=CC=CC=C1','t:s')=1;

JCHEM_CORE_PKG.GETENVIRONMENT() 

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

Oracle environment: 

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production 

PL/SQL Release 10.2.0.4.0 - Production 

CORE 10.2.0.4.0 Production 

TNS for Linux: Version 10.2.0.4.0 - Production 

NLSRTL Version 10.2.0.4.0 - Production 

JChem Server environment: 

Java VM vendor: Sun Microsystems Inc. 

Java version: 1.6.0_11 

Java VM version: 11.0-b16 

JChem version: 5.3.3 

JChem Index version: 5030300 

JDBC driver version: 11.1.0.7.0-Production 

Here is the error log:

ORA-29903: error in executing ODCIIndexFetch() routine
ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
 java.rmi.RemoteException: chemaxon.jchem.db.DatabaseSearchException: An error occurred while searching for ClC1=CC=CC=C1
Caused by:
ORA-00903: invalid table name
ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 216
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 721

and full stack trace:

Jun 8, 2010 10:48:09 AM chemaxon.jchem.cartridge.rmi.impl.RmiImplUtil handleError
SEVERE: chemaxon.jchem.db.DatabaseSearchException: An error occurred while searching for ClC1=CC=CC=C1
Caused by:
ORA-00903: invalid table name

java.util.concurrent.ExecutionException: chemaxon.jchem.db.DatabaseSearchException: An error occurred while searching for ClC1=CC=CC=C1
Caused by:
ORA-00903: invalid table name

        at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:222)
        at java.util.concurrent.FutureTask.get(FutureTask.java:83)
        at chemaxon.jchem.interop.core.search.QueryHandlerBase.stop(QueryHandlerBase.java:100)
        at chemaxon.jchem.interop.core.search.QueryHandlerBase.dispose(QueryHandlerBase.java:199)
        at chemaxon.jchem.cartridge.rmi.impl.scanresult.SearchScan.dispose(SearchScan.java:50)
        at chemaxon.jchem.cartridge.rmi.impl.JchemSearchImpl.disposeSearch(JchemSearchImpl.java:137)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:305)
        at sun.rmi.transport.Transport$1.run(Transport.java:159)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:155)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:535)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:790)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:649)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:619)
Caused by: chemaxon.jchem.db.DatabaseSearchException: An error occurred while searching for ClC1=CC=CC=C1
Caused by:
ORA-00903: invalid table name

        at chemaxon.jchem.interop.core.search.SingleQueryHandler.runJChemSearch(SingleQueryHandler.java:137)
        at chemaxon.jchem.interop.core.search.SingleQueryHandler.execute(SingleQueryHandler.java:73)
        at chemaxon.jchem.interop.core.search.QueryHandlerBase$1.call(QueryHandlerBase.java:108)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        ... 3 more
Caused by: java.sql.SQLException: ORA-00903: invalid table name

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:780)
        at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:855)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
        at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
        at chemaxon.jchem.db.StructureCache.deleteExpiredCacheRegistrationsAndLogs(StructureCache.java:1512)
        at chemaxon.jchem.db.StructureCache.loadIfNeeded(StructureCache.java:865)
        at chemaxon.jchem.db.JChemSearch.loadCacheIfNeeded(JChemSearch.java:1951)
        at chemaxon.jchem.db.JChemSearch.search1(JChemSearch.java:1678)
        at chemaxon.jchem.db.JChemSearch.search(JChemSearch.java:1516)
        at chemaxon.jchem.db.JChemSearch.access$1900(JChemSearch.java:116)
        at chemaxon.jchem.db.JChemSearch$SearchThread.run(JChemSearch.java:508)

Hi Ian,

The error appears to be generated as we try to delete rows from an auxiliary table assocated with the index created on compound_list.smiles. Either the table doesn't exists or the DELETE is executed with the identity of a user who cannot see this auxiliary table.

I am attaching a script which attempts to find out, if the table exists. Please, could you execute it using the same user as was used to execute the structure search and post the output (or send me by e-mail). The script will create a package in the user's schema called jcc_idx_stats, then prompt for the table_name (compound_list) and the column_name (smiles).

Regards,

Peter

Hi Peter,

don't appear to have the rights to download that file... is it a permissions thing on your end or my firewall blocking?

could you email it to me directly?

cheers

Ian

Hi Ian,

This problem is most probably due to a flaw in the JCC upgrade process: currently only indexes are automatically upgraded which require index data recalculation. (In this particular upgrade from 5.3.1 to 5.3.3, there was no need for index data recalculation, but the index "configuration" should have been upgraded.) This flaw will be removed in the next minor JChem version.

You can upgrade the index manually by executing:

alter index <index-name> rebuild parameters('upgradeOnly=y');

(This command will, in general, perform all upgrade related jobs -- including index data recalculation, if necessary.)

It is still puzzling how the searches could work before the reboot. Perhaps, they didn't even work...

Sorry for the inconvenience.

Peter

Hi Ian,

JChem 5.3.6 has been released with a fix for the problem of potentially missing index upgrades.

Best regards,

Peter