JC index cause TOMCAT problems

Hello again,





I am attempting to index two SMILES columns in direct succession with


JC index - on doing so I recieve the following error message:





BEGIN


*


ERROR at line 1:


ORA-29279: SMTP permanent error: 501 5.5.4 Invalid Address


ORA-06512: at "SYS.UTL_SMTP", line 21


ORA-06512: at "SYS.UTL_SMTP", line 99


ORA-06512: at "SYS.UTL_SMTP", line 222


ORA-06512: at "CSPACE.DEMO_MAIL", line 258


ORA-06512: at "CSPACE.DEMO_MAIL", line 119


ORA-06512: at "CSPACE.DEMO_MAIL", line 104


ORA-06512: at "CSPACE.QUICKBUILDPCKV3", line 1531


ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine


ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: ORA-03127: no new operations allowed until the active operation ends


ORA-06512: at line 2





Is there any way that I can poll the system to ensure that an active operation has ended - that way I can pause the program execution until such a point that I know the operation of creating the second index will complete without this error message.





I have also found that on occasion creation of an index (usually on larger tables I think) will cause TOMCAT to fall over - can this be avoided as well?





In short we find that "overloading" TOMCAT with requests can cause our process to break even though all the requests are sequential.





Many thanks for any comments,





Daniel.

This situation has arisen again and as a result our build breaks.





Do you think a pause of say 5 minutes in execution time should do the trick?





Or perhaps there is a smarter way to find out if the operation has ended - is there a system table that I can query to find this out?





Cheers,


Daniel.

Hi Daniel,





Please, could you post the the Java stack trace from the Tomcat log? Look for ORA-03127 in the Tomcat log file.





Please, could you also specify which JChem, Oracle and JDBC version you use? (The JDBC version is written out to the Tomcat log at the first JChem Catridge index/search operation.)





Thanks


Peter

JCHEM 3.2.1


ORACLE 10.2.1


ojdbc14.jar





Hi Peter,





I'm afraid the only output that I can obtain is below directly from TOMCAT window:





java.sql.SQLException: ORA-03127: no new operations allowed until the active operation ends at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)


at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:672)


at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:598)


at oracle.jdbc.driver.T2CPreparedStatement.fetch(T2CPreparedStatement.ja


va:973)


at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:291)


at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:


213)


at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run0(IndexingThread.java:57)


at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run(IndexingThread.java:40)


java.lang.Exception: ORA-03127: no new operations allowed until the active operation ends


at chemaxon.jchem.cartridge.servlets.indexing.IndexingThread.run(IndexingThread.java:43)





I've searched the TOMCAT logs and UDUMP using string "ORA-03127" and found nothing I'm afraid...





Cheers,


Daniel.

Hi Daniel,





I am trying to understand the following lines in the error message you reported:

Quote:

ORA-29279: SMTP permanent error: 501 5.5.4 Invalid Address ORA-06512: at "SYS.UTL_SMTP", line 21 ORA-06512: at "SYS.UTL_SMTP", line 99 ORA-06512: at "SYS.UTL_SMTP", line 222 ORA-06512: at "CSPACE.DEMO_MAIL", line 258 ORA-06512: at "CSPACE.DEMO_MAIL", line 119 ORA-06512: at "CSPACE.DEMO_MAIL", line 104 ORA-06512: at "CSPACE.QUICKBUILDPCKV3", line 1531

Do you use these packages (SYS.UTL_SMTP, CSPACE) intentionally, or did they get somehow in the way "by themselves"? If you use them intentionally, how do you use them? How are they called? In triggers?





I am especially confused by the fact that they appear at the top of the call stack.





Thanks


Peter

Hi Peter,





CSPACE is just the schema that I house all my packages in including the installation of the Oracle cartridge. These two index creation stages are both executed within the CSPACE schema.





SYS.UTL_SMTP is Oracle package to handle email - which you already knew.





I placed a 5 minute user_lock.sleep() command in between index creation and this seemed to do the trick...both indexes created OK without the original error message that I posted.





I can see why you indicate this other error and I have to admit to not knowing what the implications are...but I think in light of the above my original question of how one might poll the system to determine if all active operations have ended still stands? I think this is a side effect.





What do you think?





I can live with a 5 minute pause in between these operations but it would be nice to complete this in a more logical fashion.





Daniel.

Hi Daniel,





I am glad that you've found an acceptable workaround.





Cheers,


Peter

PS:


I cannot reproduce this problem in my environment and I suspect it would be very tedious to collect all the bits of information required to reproduce it.

For a while the sleep syntax seem to work OK but all of a sudden Oracle decides it is not happy with it...so I placed a commit inbetween indexing operations and as far as I can see this now does not break at this point.





I should imagine that we should both think a commit should not make any difference after a DDL statement but so far it looks to be the case.





Cheers,


Daniel.

ORA-29532: Java call terminated by uncaught Java exception:


java.lang.Exception: The following exception has been thrown by the servlet:


Exception: -1





Any ideas as to what the above excpetion indicates?





db.

Please, could you paste here the corresponding Java stack trace from the Tomcat logs.





Thanks


Peter

OK - i've appended this little request on the end of this thread as I did not want to clutter the forum anymore:





Questions:





I'd like to be able to control tomcat from within PL/SQL:





Is tomcat available is easy of course - jchem_core_pkg.getenvironment.





Startup and shutdown can be completed using java stored procedures


and command line objects (can cmd line be invoked from pl/sql?)





The difficult one is setting the cartridge password - can I do this in PL/SQL? If not can you provide the API calls in order to do this so I could do this in a java stored procedure?





This would really help automation me thinks!





Thank you,


Daniel.

Hi Daniel,





For setting password, you can use two calls:


a stored function

Code:

jchem_core_pkg.set_password(<current-user-password>)

and a stored procedure

Code:

jchem_core_pkg.use_password(<current-user-password>)

.





Both will set the current user's password in Tomcat. The difference between the two is that jchem_core_pkg.set_password will, in addition, effectively check to see if a connection can be opened from Tomcat to Oracle with the given parameters with the current user's identity.





The following query will return 0, if the password and other connection parameters in Tomcat are correct, -1 otherwise:

Code:

select jchem_core_pkg.set_password(<password-of-the-current-user>) from dual;

Cheers,


Peter

Great thanks for that - Is that a new part of the jchem_core_pkg?


Daniel.

set_password is quite new; use_password is quite old.





Peter