Using jchem_core_pkg.get_hit_count over a view

User c5c63b5c6a

09-11-2009 15:48:03

Hi,


Trying to execute the following


SELECT jchem_core_pkg.get_hit_count('CODS_USER_CIS', 'SS_UNIQUE_VERSION_STRUCTURE', 'smiles', 'c1ccccc1', 't:t simThreshold:0.9') FROM dual;


CODS_USER_CIS is a read only account with a synonym to a view called CODS.SS_UNIQUE_VERSION_STRUCTURE, this object is a view on the underlying table cods.ucs_structure which has a jchem index on the unique_smiles column.


If I change the query to be the one below it works fine.


SELECT jchem_core_pkg.get_hit_count('CODS', 'UCS_STRUCTURE', 'unique_smiles', 'c1ccccc1', 't:t simThreshold:0.9') FROM dual;


What's the best way to query this over a view and is it possible?

ChemAxon aa7c50abf8

09-11-2009 18:33:22

Hi,


Is this the error message you get with the first form:


No index found for GSK_DATA_READER.SSD_V.SMILES_V for indextype owner=JCHEM

?


Currently, only the second form works, as the gethitcount function is currently not equipped to work on views. It works only when a table name is specified as the second argument.


This function can probably be extended to work on views as well, but a general solution would certainly not be trivial. The difficult part is that I have to find out which JChem index belongs to the column in question. In the case of regular search operators (such as jc_compare), Oracle resolves the index name for me even if views are specified instead of tables. But in the case of functions like this, I am on my own: first I have to resolve the synonym to the corresponding view (easy), second I have to resolve the view's column to corresponding table column (easy for very simple views based on the view's defining text, but difficult in general) and based on the table column, I can find out the index name.


Let me know how badly you need the first form to work and if badly enough, I'll try to find a solution.


Peter


 

User c5c63b5c6a

10-11-2009 09:28:47

Yes, the error is as follows


cods_user_cis@UKINT605> SELECT jchem_core_pkg.get_hit_count('CODS_USER_CIS', 'SS_UNIQUE_VERSION_STRUCTURE', 'smiles', 'c1ccccc1', 't:t simThreshold:0.
9') FROM dual;
SELECT jchem_core_pkg.get_hit_count('CODS_USER_CIS', 'SS_UNIQUE_VERSION_STRUCTURE', 'smiles', 'c1ccccc1', 't:t simThreshold:0.9') FROM dual
                                                                                                     *
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: No index found for CODS_USER_CIS.SS_UNIQUE_VERSION_STRUCTURE.SMILES
for indextype owner=JCHEM




Elapsed: 00:00:01.71
cods_user_cis@UKINT605>


 


We can update our code to use the base table with the additional where clauses from the view.


Are there any other functions that would have a simiilar issue?


ChemAxon aa7c50abf8

10-11-2009 12:51:00

There is only one more function affected (which comes to my mind): jcf.hitColorAlign. The pattern is the same as with gethitcount: hitColorAlign expects the table and column name specified as string literals as opposed to true SQL identifiers.


Peter

User c5c63b5c6a

12-11-2009 14:40:45

Hi Peter,


Thanks for the information on the other function that only work on tables. I have managed to get the function working with a materialised view.


If you ever get this working with a view we'd be interested to know.


Thanks,


Rachel

ChemAxon aa7c50abf8

18-01-2010 09:17:22

Hi Rachel,


JChem 5.3 has been released with the two affected functions now working with views as well.


Regards,


Peter