jc_compare

Hi





I am trying to run the query below

Code:

SELECT  table1.PCN_No,table1.PMF, table1.PMWT,smilestable.SMILES AS table1_SMILES FROM    table1 LEFT JOIN    smilestable ON      smilestable.DB_NO = table1.DB_No WHERE   jc_compare(smilestable.SMILES, 'CCC1OC1','t:s') = 1

and get the error message below

Code:

ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-00942: table or view does not exist ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0 ORA-06512: at "JCHEM.COMPARE_FUNC", line 28

When I run the same query (below) without the left join, it works fine

Code:

SELECT  table1.PCN_No,table1.PMF, table1.PMWT,smilestable.SMILES AS table1_SMILES FROM    table1 JOIN    smilestable ON      smilestable.DB_NO = table1.DB_No WHERE   jc_compare(smilestable.SMILES, 'CCC1OC1','t:s') = 1

The table is quite large (over 1 million smiles)


I have tried with a small subset of the table and both queries work fine.





Do you have any ideas why this happens?





Thanks


Catherine





I am using JChem 3.1.7

Hi,





I could not immediately reproduce the problem with the LEFT JOIN.





Please, could you post the Java stack trace from the corresponding Oracle session trace file (to be found somewhere in $ORACLE_BASE/admin/udump/)?





Please, could you also post the query plan for both statements?





Thanks


Peter

Hi,





I have enclosed screen shots with the explain plans for both queries. They are identical

Quote:

Please, could you post the Java stack trace from the corresponding Oracle session trace file (to be found somewhere in $ORACLE_BASE/admin/udump/)?

No files were created in the equivalent folder





Thanks


Catherine

I am puzzled by the fact that even though the two statements have identical query plans, you get different behaviour for them. The condition containing jc_compare in the WHERE clause is applied after the join has been performed. I expect therefore jc_compare to be repeatedly executed on the elements of same result set for both statements. (From a purely syntactical perspective, the problematic statement is a left outer join, but having the restriction on the inner table in the WHERE clause practically reduces it to an equijoin. I am not sure therefore why you would want to use the left outer join syntax: the two statements would yield exactly the same result anyway, if both completed successfully.)





Would it be possible to get the query plans from sqlplus using:

Code:

explain plan for ... select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

I am more familiar with this kind of output.





Also, is it not possible to somehow get our hands on the Oracle session trace for the Java error. Have you disabled error tracing? If so, would it be possible to enable it for a short while so we can have the Java stack trace for the error? (Or did you maybe look on the wrong machine, that is why you did not find?)





Having the Java stack trace would be essential, since I find it rather hopeless to try reproducing problem -- not knowing what is the critical factor in your data what makes the problem appear.





A side note:


Is your query not too slow??? I assume that R_V_SMILES is a view and that is why domain index scan is not selected for jc_compare. You might eventually want to consider applying jc_compare directly on the structure table with the conditions in the view "externalized" into a filterQuery option of the same jc_compare.





Thanks


Peter

Thanks a lot for your help


Catherine