Problem - table or view does not exist using JChem 3.1.1

Hi


I am having a problem





jchem/jchemstreams 3.1.1


java 1.5.0_04


oracle 9.2.0.6


Red Hat Enterprise Linux ES release 3 (Taroon Update 5)


Kernel 2.4.21-32.0.1.ELsmp on an i686


tomcat 4.1.31





I have just yesterday installed Jchem on this machine, which is our production server, and last night I built the indexes on the required tables. However, this morning, most Jchem queries are failing.





select v.ISOSMILES from version v where jc_equals(v.ISOSMILES, 'c1ccccc1') = 1


ORA-29902: error in executing ODCIIndexStart() routine


ORA-00942: table or view does not exist


ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 264


ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 153


ORA-06512: at line 1





This persists also for queries using jc_compare and jc_contains.





The indexes were built on tables in 3 different schema, and each schema has another schema pointing to its objects. It is through this pointer schema that the tables are normally accessed. Last night, before I started the index build, I checked that jchem queries were working on 1 row tables in each of the schema.





At the moment, the jchem queries are only working on one of the schema. Even the pointer schema to this is not working. However, although at the moment this behaviour is reproducible, earlier today it was a different schema which seemed to be working, and that time, the pointer schema seemed also to be working.





I looked up the line of code the error was complaining about, and I cannot see any table or view, apart from dual, involved. However, I did also check some stuff related to the apparently offending line of code, which you can see in the word file which is attached.





Can you help with this?





Thanks


Mark

Mark,





My immediate suggestion would be to turn on sql trace for one of the problematic schemata in order to double check that the problem occurs at the point in code indicated by the error message.





The result of the last check in the Word document makes me believe that somehow we get the wrong error message here.





Do you have, maybe, a script that automates the creation of the setup you are using? If I could regenerate this convoluted relationship of schemata, I would have a chance to reproduce the problem.





Peter

Peter


Yes - the problem does occur at the code indicated, although I can definitely select from the indicated sequence normally. See attached trace (mark_trace.txt)





I tried to simplify it by creation of a single user with a singe table, single row, but the problem did not recur. See attached sql script. I will see if I can narrow it down for you, although if you have any ideas in the meantime, please let me know.





Mark

Mark,





What does the following statement return when you execute it as EOAI ACD (or MDL ACD):

Code:

select u.table_owner from user_synonyms u where lower(u.synonym_name) = 'jc_equals';

(Hmm. Are these schemata actually EOAI_ACD and MDL_ACD? I.e: Is the underscore missing only in the Word document?)





Peter

Peter - the schema names are as follows. I had to change them slightly to make them fit in the word table. Apologies.





EOAIACD_V15


EOAIACD (points to EOAIACD_V15 objects)





MDLACD_2005_2


MDLACD (points to MDLACD_2005_2 objects)





SSD


SCRACD (points to SSD objects)





I have connected as all six users using sqlplus and run the query you suggested. In all cases, 'JCHEM' is returned.

Peter


I have some more information for you.





The behaviour is unfortunately not consistent.





Just now, I connected as all six users and ran the query





Select v.isosmiles from version v where jc_equals(v.isosmiles, 'c1ccccc1') = 1;





In the case of EOAIACD and EOAIACD_V15, it gave the table or view does not exist error, but in the other 4 cases, it worked.





Yesterday, I saw that it worked in 3 of the schema, but not in the other 3. However, I thought I must have made a mistake, and so I just reran the tests until it seemed to behave consistently.





Nothing has changed in the database as far as I know since I reported the problem last night, so I am not sure what is causing the problem. Unfortunately, this makes it difficult to narrow down.

Mark,





I suggest to try the following simple test:


1.) Pick a schema where the search is currently not working and login with sqlplus.


2.) Start a search e.g. with jc_equals (which will probably fail then)


3.) After the search failed, run in the same sqlplus session the following command.

Code:

SELECT JCHEM.jchem_idxscan_no_sq.nextval FROM dual

This last command will probably work then.





Please, post the raw trace file (which is normally the input to tkprof). There should be two instances of the above statement in the trace file: one which failed (as part of the cartridge search operation) and one which was successful. I expect the only difference between the two statements to be the "dep" attribute (apart of course from the times). Still, it may be worth to check if there are any more indications in the trace file.





Peter

Peter





The behaviour has changed again, even though I have not changed anything on the server. The error message has changed (see below). This error is not noted in the trace, which I have included (the raw as well as the tkprof output). I do not know what this error message means. I tried recompiling JChem functions, procs and packages, but the error persisted.

Code:

C:\>sqlplus ssd@ccdlive SQL*Plus: Release 10.1.0.3.0 - Production on Wed Sep 14 14:05:14 2005 Copyright (c) 1982, 2004, Oracle.  All rights reserved. Enter password: Connected to: Oracle9i Release 9.2.0.6.0 - Production JServer Release 9.2.0.6.0 - Production SQL> select 'start' from dual; 'STAR ----- start SQL> select v.isosmiles from version v where jc_equals(v.isosmiles, 'c1ccccc1') select v.isosmiles from version v where jc_equals(v.isosmiles, 'c1ccccc1') = 1 * ERROR at line 1: ORA-29902: error in executing ODCIIndexStart() routine ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: All registered JChem service endpoints are unusable ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 0 ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 183 ORA-06512: at line 1 SQL> select jchem.jchem_idxscan_no_sq.nextval from dual;    NEXTVAL ----------         95 SQL> select 'end' from dual; 'EN --- end SQL> exit Disconnected from Oracle9i Release 9.2.0.6.0 - Production JServer Release 9.2.0.6.0 - Production

Peter


I am sorry. That error was because tomcat was not started, I think. I was used to the error telling me to start tomcat, which it used to. Anyway, I am trying again now.


Mark

Peter


Here are the files you wanted. Below is a copy of the sqlplus session, and the raw and processed trace for this session is attached. The select 'start1' from dual, etc is just to make sure I picked up the right trace.





Unfortunately, unlike the first time I posted a trace in this thread, the Oracle erorr is not mentioned in the trace, even though it did happen in the session.

Code:

C:\>sqlplus mdlacd@ccdlive SQL*Plus: Release 10.1.0.3.0 - Production on Wed Sep 14 14:23:41 2005 Copyright (c) 1982, 2004, Oracle.  All rights reserved. Enter password: Connected to: Oracle9i Release 9.2.0.6.0 - Production JServer Release 9.2.0.6.0 - Production SQL> select 'start1' from dual; 'START ------ start1 SQL> select v.isosmiles from version v where jc_equals(v.isosmiles, 'c1ccccc1') select v.isosmiles from version v where jc_equals(v.isosmiles, 'c1ccccc1') = 1 * ERROR at line 1: ORA-29902: error in executing ODCIIndexStart() routine ORA-00942: table or view does not exist ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 264 ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 153 ORA-06512: at line 1 SQL> select jchem.jchem_idxscan_no_sq.nextval from dual;    NEXTVAL ----------        100 SQL> select 'stop1' from dual; 'STOP ----- stop1 SQL> exit

Mark,

Quote:

I was used to the error telling me to start tomcat, which it used to.

This new error message is associated with new (undocumented) functionality. I will change this error message so it be more meaningful (more consistent with the docs).





Peter





PS:


The new undocumented functionality I mentionned consists of allowing the user to configure multiple Tomcat installations which act as stand-by "JChem service end points". If your primary Tomcat installation is unreachable, the first stand-by is used. If the first stand-by is unreachable the second one is tried... You can find the URL of the primary Tomcat installation in the jchem.jc_idx_property table as 'jchem.service.endPoint.url.1'. (The first stand-by is then 'jchem.service.endPoint.url.2'.)

Mark,





Was the first trace in this thread also generated using sqlplus (as the last one)? Or did you produce it using another application?





Peter

Peter


I think all the sessions were run from sql plus during this thread. However, all the traces have been generated using TOAD. This means that as soon as I have connected using sql plus, I find the session using TOAD and start a trace on it, and then execute the SQL queries back in sql plus. I then stop the trace in TOAD and find the trc file.


Mark

Mark,





Going through the sequence of SQL statements in the raw trace file, I could positively establish that the last successful statement before the error occurred was the one immediately preceding the statement

Code:

SELECT JCHEM.jchem_idxscan_no_sq.nextval FROM dual

which is missing in the last raw trace file. This means that, by all appearances, this missing statement is the one which fails.





As previously stated, I do not expect any difference between the two versions of this statement (the failed [missing in the last trace file] and successful [appearing in the last trace file]) other than the recursion depth which is 0 for the successful one and 1 for the failed one. But this should not make any difference since it only means that the failed one is executed on behalf of a statement at recursion depth level 0 -- which is the search by jc_equals. jc_equals is executed with the privileges and schema context of the calling user (in our case the user on whose behalf the session has been started) (see the the "AUTHID CURRENT_USER" modifier to the index type definition and the spec of the jchem_core_pkg package).





I always test using a jchemowner (in whose schema JChem Cartridge is installed) and a jchemuser -- both setup as described in the documentation -- with jchemuser owning the structure tables.





I sometimes test with additional jchemusers who use the structure table and index of the first jchemuser. This setup, I guess, is very similar to yours -- though probably not identical. I have just retested this kind of setup and it works for me.





One more suggestion I can offer is to modify the jchem_cart.sql script in the JChem package so that the current user identity is recorded:


1.) Modify the jchem_core_pkg.get_next_scan_num function in jchem_cart.sql as follows:

Code:

function get_next_scan_num return number as     sqname varchar2(50) := 'jchem_idxscan_no_sq';     scannum number;     uname varchar2(50);   begin     execute immediate 'SELECT username FROM user_users' into uname;     jchem_misc_pkg.trace('current user in get_next_scan_num: ' ||                          uname);     execute immediate 'SELECT ' || get_cartowner_schema() || '.'         || sqname || '.nextval FROM dual' into scannum;     return scannum;   end;

2.) Uninstall and reinstall JChem Cartridge in Oracle


3.) Execute a search with an ill-behaved schema


4.) Retrieve the name of the current user from the Oracle session trace file (the jchem_misc_pkg.trace procedure outputs its argument to this file).





There is a slim chance that we find a user with insufficient privileges and then can guess what is wrong. I do not entertain high hopes for this leading us anywhere, but who knows...





Peter

Peter


I think I have solved it. I think it was also my fault. Basically, my script to set up a jchem user involved running the script created by the following query.





select 'grant execute on ' || a.OBJECT_NAME || ' to eoaiacd_v15;' from all_objects a where lower(a.OWNER) = 'jchem' and a.OBJECT_TYPE in


('PACKAGE', 'FUNCTION', 'OPERATOR', 'TYPE', 'INDEXTYPE', 'PROCEDURE');





This was a way of doing what you suggest on the JChem installation guide under configuring JChem cartridge users, except that your guide does not include PROCEDURE.





However, when I amended this script to





select 'grant execute on ' || a.OBJECT_NAME || ' to eoaiacd_v15;' from all_objects a where lower(a.OWNER) = 'jchem' 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 eoaiacd_v15;' from all_objects a where lower(a.OWNER) = 'jchem' and a.OBJECT_TYPE in


('SEQUENCE', 'TABLE');





and ran it for all 6 schema involved, everything appears to work fine.





Incidentally, on your installation guide, you say that you need to run





call dbms_java.grant_permission( 'JCHEMUSER','SYS:java.net.SocketPermission', '127.0.0.1:8080', 'connect,resolve' );





this would be of course if tomcat were operating on 8080, which it will not normally be.





I have found that in addition you need to





call dbms_java.grant_permission( 'JCHEMUSER', 'SYS:java.net.SocketPermission',


'servername', 'resolve' );





call dbms_java.grant_permission( 'JCHEMUSER', 'SYS:java.net.SocketPermission', 'serverip:port', 'connect,resolve' );





In future I think I will just try the install_user.sh script, rather than the manual method, as presumably this is kept up to date with JChem versions, and you test this installation script.





Sorry to have wasted your time.


Mark

Mark,

Quote:

However, when I amended this script to

I do not understand how the amended script solved the problem, but I am glad it works.

Quote:

Incidentally, on your installation guide, you say that you need to run call dbms_java.grant_permission( 'JCHEMUSER','SYS:java.net.SocketPermission', '127.0.0.1:8080', 'connect,resolve' );

This single command works for me. Of course, you have to replace user name, host and port appropriately as indicated in the user guide.

Quote:

In future I think I will just try the install_user.sh script, rather than the manual method, as presumably this is kept up to date with JChem versions, and you test this installation script.

This script is not kept up-to-date. I will probably remove it from future versions. I originally put this script into the package to only provide some help/hints and not to provide a ready-to-use utility. The immediate reason for this is that a second script for Windows users should have also been created which would have been much more complicated to implement. A better alternative could be to implement this functionality in Java.





However, I also have a "less immediate" reason for being reluctant to provide a "supported" utility for the purpose: the procedure of granting privileges is an area which may significantly vary from one site to another depending on each user's respective security policy and application environment. Just take the solution you described above: the only addition I can discern in your amended script is the execute privilege on package bodies. Based on my (certainly limited) experience and on the Oracle documentation, I cannot establish the logic linking the problem and the solution. And (until evidence to the contrary) this makes me believe that this must have been a site specific problem.





Peter