"ORA-01795: maximum number of expressions in a list is 1000"

 

Hi,

We use JChem cartridge 5.9.0, Oracle RDBMS 11.2.0.3 x86_64.

I've got error during jc_tanimoto search with lower similarity (0.4):

ORA-29902: error in executing ODCIIndexStart() routine

ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: 

java.rmi.RemoteException: ORA-01795: maximum number of expressions in a list is 1000

ORA-06512: at "JCHEM_BUNDLE.JCHEM_CLOB_PKG", line 81

ORA-06512: at "JCHEM_BUNDLE.JC_IDXTYPE_IM", line 362

...

 

Also I've tried use jc_compare(... 't:t similaryTreshhold:0.4 maxHitCount:100') with the same result.

Can I apply some workaround for that?

 

Pavel

Hi Pavel,

Please, could you send us (or upload) the log files from jchem/cartridge/logs? This would be an immense help to us in finding out what happens.

Many thanks

Peter

This error occurs only when I tried to get result of jc_tanimoto in output.

So, how can I return result of jc_tanimoto (as score of similarity) without call of jc_tanimoto?

In other words I can use some workaround to avoid this error:

select unit_id, 1 as score
from chem_structs
where jc_compare(structure, <query>, 't:t simThreshold:0.4 dissimilarityMetric:tanimoto maxHitCount:100') = 1;

but when I try to get score. I got error:

 

select unit_id, jc_tanimoto(structure, <query>) as score

from chem_structs
where jc_compare(structure, <query>, 't:t simThreshold:0.4 dissimilarityMetric:tanimoto maxHitCount:100') = 1;

 

Pavel

pkovacs wrote:

Hi Pavel, Please, could you send us (or upload) the log files from jchem/cartridge/logs? This would be an immense help to us in finding out what happens. Many thanks Peter

Sure, Peter.

File has been attached.

Many thanks for this, Pavel!

Peter

I think this issue relates to some SQL query with condition of IN (<list of more than 1000 elements>).

It's better use binding variables in such purposes. It will be better for performance too. Also it is possible use temporary tables to store intermediate results for session.

Pavel

Hi Pavel,

The problem appears to be in cache loading. Could you reproduce the error while setting the log level to FINER?

Thanks,
Tamás

cheese wrote:

Hi Pavel, The problem appears to be in cache loading. Could you reproduce the error while setting the log level to FINER? Thanks, Tamás

Szia, Tamas.

I'll try reproduce it soon.

Pavel

Sorry.

I was very busy and my "soon" bacames "very very NOT soon".

Hi Pavel,

In the meantime we have found the suspicious part in the code. The fix is released in JChem 5.9.4, you can try whether it solves your problem.

Al the best,
Tamás

Thanks a lot, Tamas.

I've downloaded last JChem Cartridge package for tests already. I'm sure that everything will be ok.

Sorry, that I did not sent you feedback before.

Pavel