Unable to promote Oracle View as Chemistry entity

Good morning

I have 2 Oracle users A & B

A owns a JChem cartridge table and a view based on this table

A & B can both run a sql chemical query against the table and the view using jc_compare

I have promoted the table and the view to entities in Instant JChem 5.9.3

The table is recognized as a chemistry entity and I can run a chemical search against it.

However the view is not recognized as a chemistry entity, I can fix the display by changing the renderer to be a structure, but I can't run a chemical query against it.

As user B, I  have run

select* from all_views where owner = 'A' 

select
 
 
 
 
 
i.index_name, i.table_owner, i.table_name, c.column_name from all_indexes i, all_ind_columns cwhere i.ityp_owner = 'JCHEM' 
 
 
 
and i.owner = 'A' 
 
 
 
and i.ityp_name='JC_IDXTYPE' 
 
 
 
and i.index_name = c.index_name
 
 
 
 
 

Hi Catherine

It might be that the view syntax is not being recognised as a view of a table with a cartridge index. This is done by inspecting the SQL for the view definition. A bit more info can be found here: 
http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/editing_database/StructureBased_views.html 

Could you post the view defininition, or send it by email?

Tim

Hi Tim

Please see below the view definition

The view is created with selected fields from one table only

Thanks

Catherine

CREATEOR REPLACE FORCE VIEW EVOSRC.CT_LAB_CHEMICAL_VIEW 
(
LAB_CHEMICAL_ID, 
SMILES, 
IUPAC_NAME, 
BUILDING, 
LAB, 
CATALOGUE_NUMBER, 
SUPPLIER_NAME, 
CURRENT_QUANTITY, 
CURRENT_QUANTITY_UNIT, 
INITIAL_QUANTITY, 
INITIAL_QUANTITY_UNIT, 
IFS_PROJECT_ID, 
DESCRIPTION, 
 CLASS, 
LAST_UPDATED
)
AS
 SELECT LAB_CHEMICAL_ID, 
SMILES, 
IUPAC_NAME, 
BUILDING, 
LAB, 
CATALOGUE_NUMBER, 
SUPPLIER_NAME, 
CURRENT_QUANTITY, 
CURRENT_QUANTITY_UNIT, 
INITIAL_QUANTITY, 
INITIAL_QUANTITY_UNIT, 
IFS_PROJECT_ID, 
DESCRIPTION, 
 CLASS, 
LAST_UPDATED
 FROM CT_LAB_CHEMICAL; 
　
GRANTSELECT ON EVOSRC.CT_LAB_CHEMICAL_VIEW TO LAB_CHEM;

Hi Catherine,

I'm looking at the definition of your view. What version of IJC are you using? There have been some fixes in this areas for IJC 5.10.

Thanks,
-vita

Hi Catherine,

the SQL parser in IJC 5.9.x is a bit dumb and understand only a limited SQL syntax. Please define your view strictly as this:

create view XYZ as select ... 

The select part can contain usual stuff, but there must be nothing else after that part. In your case there was the 'grant ...' part that causes the problem.

I fixed the problem in trunk and should be able to squeeze the fix in IJC 5.10 as well. With the fix your SQL parses ok.

Thanks,
-vita

Good morning

Thanks for your reply

I needed the 'grant' option as I am trying to connect to IJC as the user defined in the grant

I have now been able to get it to work and recognize the view as a structure entity but I had to give extra privilege to the user connecting to IJC (create view) and create the view in the oracle schema connecting to IJC

I will look forward to try this out in the next version.

Thanks for your help

Catherine