Update 5.2.02 -> 5.2.03.1 Exception

Hi,

During update procedure to 5.2.03.1 I've got the follow exception

...skipped...
INFO: JC_IDXTYPE successfully processed.
INFO: Loading PL/SQL script jcart.jar into jchem's schema...
INFO: Loading PL/SQL script jchem_util.sql into jchem's schema...
INFO: Loading PL/SQL script privman.sql into jchem's schema...
INFO: Dropping staging schema...
INFO: Using classpath: C:\jchem.5.2.03.1\lib\jchem.jar;
INFO: Server process stop-ed.
java.sql.SQLException: ORA-25191: cannot reference overflow table of an index-organized table
ORA-06512: at "JCHEM.PRIVMAN_PKG", line 40
ORA-06512: at "JCHEM.PRIVMAN_PKG", line 99
ORA-06512: at "JCHEM.PRIVMAN_PKG", line 46
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
        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:1030)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:936)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:298)
        at chemaxon.jchem.cartridge.install.Schema.executeSql(Schema.java:1482)
        at chemaxon.jchem.cartridge.install.Installer.grantBasicJccPrivileges(Installer.java:218)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:62)
        at chemaxon.jchem.cartridge.install.Upgrader.upgrade(Upgrader.java:46)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:258)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.upgrade(UpgradeCmdLine.java:55)
        at chemaxon.jchem.cartridge.install.UpgradeCmdLine.main(UpgradeCmdLine.java:531)

All the prior operations finished successfully.
Despite the exception, the server can be started and it seems to perform as expected.
As a result of the exception the jchem scheme has a table called 'SYS_IOT_OVER_82494' without any columns in it. If I try to drop the table directly in Oracle, I get the same exception.

The config includes:
Oracle 11.1.0.7.0 on W2008 x64
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

Thanks,
Val

Hi Val,

Have you applied Oracle patch 8260301 (http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=8260301)?

Thanks

Peter

Hi Peter,

Yes, the patch bundle mentioned above was applied.

Thanks,
Val

Hi Val,

I could not reproduce the problem on Linux x86_64. I will try and reproduce the problem on Windows Server 2008 64-bit.

In the meantime, you can go on using the upgraded installation, as, based on your description, the only step missing from the upgrade is the re-grant of privleges on the JChem Cartridge program objects. If you don't experience privilege related problems, you should be fine with the upgrade.

Cheers,

Peter

Val,

Just one thing that has come to my mind: Did you create any database objects (tables or indexes) manually in the jchem schema?

Thanks

Peter

Hi Peter,

Yes, I've created several tables and indices manually in the jchem schema.
You think this can be the cause of the problem?

As I mentioned before, the instance itself is working without any noticeable problems. I wouldn't even mention the exception, if it wasn't for InstantJChem to stop working with 11.1.0.7 JDBC after the update. So I thought these two issues are connected and decided to start with the cartridge. I will post another message on IJC in appropriate section.

Cheers,
Val

Hi Val,

I discourage users from putting anything in the JChem-owner's schema. It is meant to hold the JChem Cartridge related database/program objects. JChem Cartridge utilities cannot currently tell apart JChem-objects from non-JChem objects in the same schema. One way close at hand to solve the problem of telling apart could be storing a list of JChem objects created during installation and upgrade to be used by JChem Cartridge utilities. But currently, this is a plan only (FS#8802).

I'd suggest to use another schema for your application-/site-specific database objects.

Thanks

Peter