Hi Ant,
There appears to be at least three distinct aspects to your suggestion:
- What is contained in the error message
- How the error is raised (let Oracle raise an ORA-29532 error or replace that error with my own PL/SQL exception)
- What is traced/logged
What is contained in the error message
Whether a message is meaningful or not is sometimes subjective and/or dependent on the exact context the error is occurring in. One could argue that the error message you presented in your initial post is meaningful in general, even if it contains some "noise" not directly useful to the end user. (Extra information which can be considered "noise" in most, but not necessarily all scenarios.)
I agree that error messages should contain only the necessary pieces of information. But what is necessary may depend on the user of the error message. For example, should the error message about a structure format error contain the structure itself? Not necessarily, because the error message can tell, if the error is about the query or the target. But what about this scenario:
select count(*) from nci_1k where jc_contains(structure, 'Linux rulez!') = 1 or jc_contains(structure, 'Zazi dans le metro') = 1
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20001: Invalid query syntax, SMARTS entered is invalid
ORA-06512: at "PKOVACS_TRUNK.JC_IDXTYPE_IM", line 207
Which query structure is meant here? The query for the first JCC operator or for the second?
Shouldn't we also include the reason why the structure is incorrectly formed? With any structure of some complexity, the user is likely to be grateful having an indication of the cause so she instantly knows how to fix the problem. The error you reported in your initial post is a case in point.
Wouldn't there be some more complicated cases, when it would make everybody's life easier if the message included some indication of exactly where in the code the error occurred?
How the error is raised?
If a Java stored procedure generates an exception, the exception will be logged in the Oracle session trace no matter how the exception is handled in the calling PL/SQL code. In order to provide the ability for users to suppress (or enable) logs in the Oracle session trace, Java stored procedures (JSPs) should not throw exceptions.
The only way that appears to be left for JSPs to pass error conditions to their PL/SQL callers appears to
- store the error message in the session scope (e.g. a package variable such as jchem_core_pkg.last_error) and
- indicate through return values that an error has occurred.
If a JSP returns an error, the caller would raise a PL/SQL exception using the error message stored in the session's scope. This way the user will be able to turn off or on at her discretion the logging of JSP errors to the Oracle session trace file. (When turned on, the full exception will be logged as opposed to the shortened message included in the exception raised on the PL/SQL side. PL/SQL appears to trim error messages to 4000 characters, which is often not enough, if, for example, badly formed MRVs or SDFiles are to be made available somehow. For the same reason, some of the current error messages must be rearranged so that the reason comes first, then the structure second [if any], then the failing SQL third [if any].)
There is a potential problem with session-scoped variables: during execution of SQL queries containing calls to multiple JCC operators, the operators may be executed in an interleaved fashion (typically as part of two domain index scans) and if two or more operators fail on the Java side, one PL/SQL caller may trigger the abortion of the top level SQL call with the error message of the other failed call. While the error message will still be valid from the top-level perspective, it may be inconsistent with the PL/SQL stack presented in the final error message. In other words: the error message will appear in the wrong "(sub-)context". The implementation of the "error message passing" has to take care of this problem (by somehow associating the error messages with the corresponding index scan context where applicable). -- Or a more comfortable approach: we simply say that errors occurring while the index scan is already underway are unrecoverable and let JSP exceptions go through.
What should NOT be traced / logged?
- All errors due to invalid arguments input by the user. Error types directly attributable to invalid user input:
- Invalid structure format
- Invalid Chemical Terms expression
- Invalid search option
- Invalid option for JChem table DMLs
- "Fake errors" providing feedback on a condition to the user short of a better mechanism:
- Insertion of duplicate structures (when duplicate filtering is turned on)
- Indexing duplicate structures (when duplicate filtering is turned on)
Regards,
Peter