First, let me explain shortly how my application is working:
The application handles deals, which are stored as an XML document in our database (Oracle 11g). The table that contains these information is defined like that:
table T_MYDEALS (
DEAL_ID number(9, 0) not null,
DEAL_XML xmltype
)
When we update or insert new items in this table, we have a trigger that will read (using XPath) this XML, and populates some others metadata tables.
Everything works fine, except on my machine.
Now the problem
When I run the application on my machine (i.e. the Tomcat run within my Eclipse, but connected to the Homologation DB), the trigger fails with the following error:
WARN [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1722, SQLState: 42000
ERROR [org.hibernate.util.JDBCExceptionReporter] ORA-01722: invalid number
ORA-06512: at "MY_SCHEMA.AFTER_R_INSERT_MYDEAL", line 628
ORA-04088: error during execution of trigger 'MY_SCHEMA.AFTER_R_INSERT_MYDEAL'
I'm sorry, I can't put my trigger here, for security reasons. Just note that the line 628 is at the end of the trigger code.
My tests
So I tried to understand why this error happened on my machine (as it only happen on my machine, none of my colleague encounter this issue). I can't say when it starts to stop working, maybe when I changed my computer recently?
First, I check the source code, then my DB connection, but everything seems correct.
I also switched in debug mode to have a look in the XML sent to the DB, or at least in our HibernateXMLType (which is an extension of org.hibernate.usertype.UserType, used to transform our XML into data readable by Oracle). But nothing wrong found here neither.
I save you many tests done on my side, but one of my latest tests was to get a fresh Tomcat server, and deploy a WAR that is deployed in an environment (Homologation for ex.). Then, I executed the same tests, but the trigger is still failing.
So far, I have eliminated the following suspects:
- The source code, as I also tested the application from a WAR that is deployed on an environment and which is working correctly;
- The DB itself, as I am connected to the same DB as the working environment. I also tried with another DB, the result is the same.
- The data used for my tests, as it works if I try to save the same deal, but using the Homologation environment.
- JDK, as I also changed the JDK for a new one;
- Eclipse, as my latest tests were done outside Eclipse;
- Tomcat server, as I also tried on a new Tomcat.
What I am wondering is if my Windows XP environment has some specific encoding configuration, which "transforms" some data within the XML and make them
My questions
- What are the possible elements that I may have forgotten in my tests?
- Is there a way to know exactly the XML processed by the Oracle trigger (if possible without installing anything on the Oracle instance, as I don't have any control on it)?
I know that I don't provide a lot of information, but if you can give me some hints, or ideas, I would be really grateful!
Regards.
Technical information: Java 1.6, Oracle 11g, Tomcat 5.5.23, JSF 1.2, Hibernate 3.3
Could it be localization? On your system you're populating the xml with "1,5" instead of "1.5" for example. The error that is reported by Hibernate clearly points in that direction. You could disable the trigger and see what the resulting xmltype is in that table. And if you or one of your colleagues can access the database through an SQL Developer like client you could try and run the code in the trigger "manually".
See here for info and possible actions when you encounter ORA-01722.