ORA-14551

I have just upgraded the cartridge from 5.2.3.1 to 5.3.0.1 and I've encountered an error.

When I try the following from a user that does not have any jchem tables created with jcman, but have an old table.

"select jc_evaluate_x((select jc_molconvert((select molfile  from hive.tempmolfile where pkey=93363325),'smiles') from dual),'chemTerms:dotDisconnectedFormula') from dual"

I get the following error.

"ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-14551: cannot perform a DML operation inside a query".

This is the information I get from trace.

"*** 2010-01-30 11:45:49.429
returning string of 8 characters...
2010-01-30 11:45:49.429 [10172903, MIMI, MIMI]: DEBUG - chemaxon.jchem.cartridge.JFunctions: execFunctions(sqlOperator=evaluate_x, target=C1CCCCC1, query=null, optionsString=chemTerms:dotDisconnectedFormula, rid=null, idxSchema=null, idxName=null, idxPartition=null, tblSchema=null, tblName=null
2010-01-30 11:45:49.446 [10172903, MIMI, MIMI]: DEBUG - chemaxon.jchem.cartridge.JcMetaDataFunctions: Executing: SELECT u.prop_value || g.prop_value as prop_value FROM mimi.jc_idx_property u, jchem.jc_idx_property g WHERE u.prop_name = ? AND g.prop_name = ?
2010-01-30 11:45:49.447 [10172903, MIMI, MIMI]: DEBUG - chemaxon.jchem.cartridge.JcMetaDataFunctions: Executing: SELECT prop_value FROM mimi.jc_idx_property WHERE prop_name = ?
2010-01-30 11:45:49.486 [10172903, MIMI, MIMI]: Problem with inserting: name=dummy, value=dummy
Exception in thread "Root Thread" oracle.jdbc.driver.OracleSQLException: ORA-14551: cannot perform a DML operation inside a query
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java)
    at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)
    at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java)
    at oracle.jdbc.driver.T2SPreparedStatement.executeForRows(T2SPreparedStatement.java)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java)
    at chemaxon.jchem.cartridge.JcMetaDataFunctions.insertProperty(JcMetaDataFunctions.java:770)
    at chemaxon.jchem.cartridge.JcMetaDataFunctions.insertMasterProperty(JcMetaDataFunctions.java:858)
    at chemaxon.jchem.cartridge.JcMetaDataFunctions.setMasterProperty(JcMetaDataFunctions.java:792)
    at chemaxon.jchem.cartridge.JcMetaDataFunctions.setDefaultProperty(JcMetaDataFunctions.java:1050)
    at chemaxon.jchem.cartridge.structs.JCartDefaultProperties.initUserValidityTimestamp(JCartDefaultProperties.java:271)
    at chemaxon.jchem.cartridge.structs.JCartDefaultProperties.getInstance(JCartDefaultProperties.java:246)
    at chemaxon.jchem.cartridge.JFunctions.execFunction(JFunctions.java:452)
    at chemaxon.jchem.cartridge.JCFunctionsClob.execFunctionC(JCFunctionsClob.java:77)".

Dropping the old jchem table in that schema and retrying the sql gives the following in trace

"returning string of 8 characters...
2010-01-30 12:53:04.328 [10173808, MIMI, MIMI]: DEBUG - chemaxon.jchem.cartridge.JFunctions: execFunctions(sqlOperator=evaluate_x, target=C1CCCCC1, query=null, optionsString=chemTerms:dotDisconnectedFormula, rid=null, idxSchema=null, idxName=null, idxPartition=null, tblSchema=null, tblName=null
2010-01-30 12:53:04.353 [10173808, MIMI, MIMI]: Failing statement: CREATE TABLE mimi.jc_idx_property(prop_name VARCHAR2(4000) unique, prop_value VARCHAR2(50), prop_value_ext LONG RAW)
Exception in thread "Root Thread" oracle.jdbc.driver.OracleSQLException: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java)
    at oracle.jdbc.driver.T2SConnection.check_error(T2SConnection.java)
    at oracle.jdbc.driver.T2SPreparedStatement.checkError(T2SPreparedStatement.java)
    at oracle.jdbc.driver.T2SPreparedStatement.executeForRows(T2SPreparedStatement.java)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java)
    at chemaxon.jchem.cartridge.JcMetaDataFunctions.execDDLStatement(JcMetaDataFunctions.java:1167)
    at chemaxon.jchem.cartridge.JcMetaDataFunctions.initMetadataMaybe(JcMetaDataFunctions.java:1120)
    at chemaxon.jchem.cartridge.structs.JCartDefaultProperties.initUserValidityTimestamp(JCartDefaultProperties.java:269)
    at chemaxon.jchem.cartridge.structs.JCartDefaultProperties.getInstance(JCartDefaultProperties.java:246)
    at chemaxon.jchem.cartridge.JFunctions.execFunction(JFunctions.java:452)
    at chemaxon.jchem.cartridge.JCFunctionsClob.execFunctionC(JCFunctionsClob.java:77)
"

It works if I do it from a user that has jchem tables, but the majority of the users do not.

This worked in 5.2.3.1

Have I missed something in the upgrade ?

This is related to the default properties feature which was extended in 5.3 to users as well. In earlier releases, default properties could be defined only at the cartridge installation level. In the current implementation (5.3), all users could, in principle, have their customized default properties. (This is kind of an undocumented new feature.) But in the light of this problem (and of the implications its fix appears to entail at first glance), I might need to limit the custom default properties to index owners only. (Thinking about this a bit more, it probably doesn't make much sense anyway for the index owner and the index user to have two different settings for default standardization for example. And then a non-index-owner would have to pick the installation-global default... So I think default properties can not really be more granular in scope than installation-global.)

I haven't had time yet to extensively research the problem, but I think that making sure that all JChem Cartridge users have some JChem index might be a workaround for this problem.

Peter

Thank you for the fast response Peter.

I made a fast workaround by using my own algorithm for calculating the dotDisconnectedFormula. I'll experiment with writing a procedure for calculating this, that resides in a schema that has jchem tables to see if that works better since I want to use your algorithms for calculations.

If you want to keep the customization feature, maybe you could extend the call with a parameter to point to a schema with default properties like you have for dml, and have the global installation schema as default value ?

Regards,

Mikael

Hi Mikael,

The bug will be fixed in JChem 5.3.1. (I decided to keep the undocumented extension of the default properties feature -- with the limitation that a session must be restarted when the first user-specific property is set in the user's schema so the session "notices" that it has user specific properties to check.)

Thanks for reporting the problem and sorry for the inconvenience.

Peter

The bug will be fixed in JChem 5.3.1.

Hi Mikael,

I would like to inform you that we had to make an urgent patch release that was named 5.3.1, therefore the fix promised in this topic will be included only in the patch release coming at the end of March (probably named 5.3.2).

Best regards,

Akos

Hi Mikael,

JChem Cartridge has been released with the fix.

Peter