I stumbled upon a very brain-f**k error on Oracle 10g on these days.

Context: the following query [sql]SELECT * FROM (
SELECT TO_NUMBER(CUSTOMER_ID) AS SNDG FROM BAD_CODES_TABLE WHERE
AND I_LIKE=UPPER(‘STATIC_CONDITION’) AND CUSTOMER_ID NOT LIKE ‘%P%’ ) S WHERE TO_NUMBER(S.SNDG) >2000[/sql] could trigger a Invalid number if CUSTOMER_ID column contains invalid numbers.

Why?

Well…if you ask to “explain plan”, you will get something like

• a table full scan
  • Filter Predicates AND
    • I_LIKE=UPPER('STATIC_CONDITION')
    • TO_NUMBER(S.SNDG) >2000
    • CUSTOMER_ID NOT LIKE '%P%'

Working for a very big customer, I found a very nasty interaction between Sun HttpClient (JDK 1.4) and Http  Load Balancers.

In a complex network environment, sometimes you can experience low level TCP/IP comunication errors, because sometimes HttpClient get confused and hangs.

The bad behavior of Sun HttpClient is well known: some guys suggested me to use the Axis Web Client. Anyway you can solve the issue adding these three parameters to the JVM launch line