Sql Query running in oracle SQL Developer But when i run the query from my JAVA program it is giving Sql exception

Question

I am fetching some data using the following query

SELECT 
t.value_date,t.settlement_date, 
 (CASE WHEN NVL( t.settlement_date, '01-01-9999')='01-01-9999'
 THEN t.value_date
 ELSE t.settlement_date
 END) AS modified_value_date, 
 t.instrument_pk, 
 bct.base_ccy_pk, 
 i.contract_size, 
 lc.min_trading_unit AS min_trading_unit_lc, 
 bc.min_trading_unit AS min_trading_unit_bc 
FROM transaction t, 
 bc_transaction bct, 
 instrument i, 
 instrument lc, 
 instrument bc
WHERE t.status = 'NORMAL' 
AND t.fund_pk = 99436 
AND t.instrument_pk = 235342 
AND t.instrument_pk = i.instrument_pk 
AND t.local_ccy_pk = lc.instrument_pk 
AND bct.transaction_pk = t.transaction_pk 
AND bct.base_ccy_pk = bc.instrument_pk 
AND t.value_date >= to_date('20121123000000','yyyymmddhh24miss') 
AND t.transaction_type = 'SECURITY_TRADE' 
ORDER BY modified_value_date, 
 t.processing_order, 
 t.txn_reference_no;

Problem is when i m running the above query in Oracle SQL Developer it's giving the proper result. But when i am trying to run the query from my java program using PreparedStatement it is producing an exception like java.sql.SQLException: ORA-01843: not a valid month

My problem got solved when i replace the date in CASE bolck with

(CASE WHEN NVL(t.settlement_date,to_date('99990101000000','yyyymmddhh24miss'))=to_date('99990101000000','yyyymmddhh24miss') 
 THEN t.value_date 
 ELSE t.settlement_date 
 END) AS modified_value_date

Now My questions is

1. why there is such kind of discrepancy?If the problem is with the to_date() then it should have given the same exception in SQL developer also.

2. The problem does not end here I saw when I m debugging my program from eclipse again the same query is running perfectly without to_date() in NVL block the I checked and found that my JDK is 1.5 but the JRE was 1.6.Then I changed my JRE to 1.5 and debug the same java program and found that it is producing the same exception ,for the same query without to_date() in NVL block, at the time of debugging.

   Now I m completely messed up why such kind of discrepancy between JRE 5 and JRE 6?

CAN ANYONE HELP ME?

• Iswanto San: can you post your java code?
• A.B.Cade: This whole CASE WHEN NVL(... doesn't make sense, why not just NVL( t.settlement_date, t.value_date) ? or CASE WHEN t.settlement_date is null then ...
• Angshuman: A.B.Cade You are right.There is no need to use CASE WHEN NVL(.... but my concern is something else.If i use my above sql it is running well in SQL DEVELOPER.but from my code it is producing exception java.sql.SQLException: ORA-01843: not a valid month.if my sql is not right it should have given the same exception in sql developer also....
• A.B.Cade: @AngshumanDey, for that you already got an aswer - when you use NVL with a date and a string oracle implicitly converts your string to a date according to NLS_DATE_FORMAT which can be different in different sessions opened by different tools, always use the to_date function to explicitly convert your string-dates. stackoverflow.com/a/14847616/1083652

Answer 1

It is not about JRE version but about your session settings. You have specified string and left Oracle session to implicitely convert to date using NLS setting (likely NLS_DATE_FORMAT).
Do yourself a favor - never leave hardcoded value to depend on external setting.
You can use DATE'9999-01-01' instead.