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'
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.index_name = c.index_name
and this returns the view that I am trying to query
and the index of the table smiles column.
Does user B need extra permission(s) to be able to recognize the view as a chemistry entity from IJC ?
Let me know if you need more information
Please see below the view definition
The view is created with selected fields from one table only
CREATEOR REPLACE FORCE VIEW EVOSRC.CT_LAB_CHEMICAL_VIEW
GRANTSELECT ON EVOSRC.CT_LAB_CHEMICAL_VIEW TO LAB_CHEM;
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.
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 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