A problem using a JChem index with multiple oracle user

I have the same problem I have reported earlier by email while trying to use JChem indexes by multiple Oracle users.





In my case the JChem objects are owned by the schema 'JCHEM_CARTRIDGE' with DBA privileges. I also have a 'user' schema say 'JCHEM_USER' with normal privileges and it has a role with permissions generated by the SQL query





select 'grant execute on ' || a.OBJECT_NAME || ' to JCHEM;' from all_objects a where a.OWNER= 'JCHEM_CARTRIDGE' and a.OBJECT_TYPE in ('PACKAGE', 'FUNCTION', 'OPERATOR', 'TYPE', 'INDEXTYPE', 'PROCEDURE', 'PACKAGE BODY', 'TYPE BODY') union all select 'grant select on ' || a.OBJECT_NAME || ' to JCHEM;' from all_objects a where a.OWNER = 'JCHEM_CARTRIDGE' and a.OBJECT_TYPE in ('SEQUENCE', 'TABLE');





as you have recommended. Now when I index a table in JCHEM_CARTRIDGE and index it as JCHEM_CARTRIDGE, 'JCHEM_USER' is able to use it without any problems. However, it doesn't work if I try the opposite; when I create and index a table with JCHEM_USER and then try a structural search as JCHEM_CARTRIDGE here is what I get





*


ERROR at line 1:


ORA-29925: cannot execute JCHEM_CARTRIDGE.JC_IDXTYPE_IM.ODCIINDEXSTART


ORA-00904: : invalid identifier





while JCHEM_USER is able to use the index. It seems that JCHEM_USER has all the permissions and JCHEM_CARTRIDGE is DBA so it has access to all the tables. Can you please help me to figure out what is wrong with my setup?





Thanks


Hayk


GNF

Hayk,





Is the 'JCHEM_CARTRIDGE' user able to use structure tables in its own schema?





Peter

Yes, JCHEM_CARTRIDGE is able to use the indexes on the tables in its own schema.

Hayk,

Quote:

* ERROR at line 1: ORA-29925: cannot execute JCHEM_CARTRIDGE.JC_IDXTYPE_IM.ODCIINDEXSTART ORA-00904: : invalid identifier

Is this the entire error message? Please, could you check the Oracle trace for this session (in $ORACLE_BASE/admin/<your-db>/udump). Is there maybe something interesting (or more) in the session trace file?





Peter

There isn't much useful information in the trace file. I would say there is too little information: even the statement I'm trying to execute is not there. I attach the file anyway.

What is the command you are trying to execute?





Do you issue it using sqlplus or it is generated by an application?





Is this really really the entire error message you get? Does it begin with an asterix?????





Try to grant the same privileges to JCHEM_CARTRIDGE as to JCHEM_USER.

Here is the full output of SQL Plus





SQL> select * from structure where jc_tanimoto(jc_smiles,'c1ccccc1')>0.9;


select * from structure where jc_tanimoto(jc_smiles,'c1ccccc1')>0.9


*


ERROR at line 1:


ORA-29925: cannot execute JCHEM_CARTRIDGE.JC_IDXTYPE_IM.ODCIINDEXSTART


ORA-00904: : invalid identifier








SQL>





I have granted all the privileges I could think of. Eventually I ended up having both of my users with DBA role so I don't think granting more privileges will help.

I have just noticed the little ':' in the error message. The message says ':' is an invalid identifier. In the ODCIIndexStart routines I cannot find anything even remotely resembling a ':' identifier. (And there is obviously none in your SQL statement.)





On the Oracle metalink you can find a number of reports of the

Quote:

ORA-00904: : invalid identifier

error message. Reading these reports I have the general impression that Oracle emits this obscure error message for certain kinds of unexpected/unhandled/error situations. I suggest to look at these reports (such as http://www.metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=652012.995 ), maybe you can find some hints in them. If you cannot, I suggest to raise a tar with your Oracle technical support.