ORA-01858 Oracle Date Problem while inserting from java

Question

I have a code in my application which inserts java.sql.Timestamp into oracle date column. This code works fine. But occasionally i get the error :

ORA-01858: a non-numeric character was found where a numeric was expected.

When i see the log, similar inserts executed at the same time are successful and then all of a sudden one particular operation fails. When i look at the format between different statements, they are similar. Could not guess the problem.

The first statement failed. But the second has got inserted successfully.

INSERT INTO OMTM_EXECUTE_REPORT(er_id_pk,er_oms_order_id,er_orig_oms_order_id,er_esis_no,er_transaction_no,er_exec_trans_type,er_exec_type,er_ord_status,er_order_rej_reason,er_cust_account,er_settle_type,er_fut_settle_dt,er_security_type,er_symbol_code,er_action,er_ord_quantity,er_ord_type,er_price_per_share,er_stop_price_per_share,er_currency_id,er_time_in_force,er_expire_time,er_last_trd_shares,er_traded_price_per_share,er_leaves_quantity,er_cumulative_qty,er_average_price,er_transact_time,er_trade_date,er_text,er_can_rej_response_to,er_can_rej_reason,er_disclosed_quantity,er_fill_quantity,er_read_flag,er_settle_flag,er_exec_rep_time,er_modified_datetime)
      VALUES      (OMSQ_TM_ID_PK.NEXTVAL,5168,0,08434308,0436594708434308,0,2,2,null,0079027076,9,null,null,ALDAR,BUY,100000,2,5.33,0.0,0,0,2009-12-17 00:00:00.0,100000,5.33,0,100000,5.33,2009-12-17 06:01:54.0,2009-12-17 06:01:54.0,Hold Created Inspite of Insufficient Bal,null,null,0,0,'N','N',SYSDATE,SYSDATE)


INSERT INTO OMTM_EXECUTE_REPORT(er_id_pk,er_oms_order_id,er_orig_oms_order_id,er_esis_no,er_transaction_no,er_exec_trans_type,er_exec_type,er_ord_status,er_order_rej_reason,er_cust_account,er_settle_type,er_fut_settle_dt,er_security_type,er_symbol_code,er_action,er_ord_quantity,er_ord_type,er_price_per_share,er_stop_price_per_share,er_currency_id,er_time_in_force,er_expire_time,er_last_trd_shares,er_traded_price_per_share,er_leaves_quantity,er_cumulative_qty,er_average_price,er_transact_time,er_trade_date,er_text,er_can_rej_response_to,er_can_rej_reason,er_disclosed_quantity,er_fill_quantity,er_read_flag,er_settle_flag,er_exec_rep_time,er_modified_datetime)       
VALUES      (OMSQ_TM_ID_PK.NEXTVAL,5170,0,08434400,0436599708434400,0,2,2,null,0059729784,9,null,null,SOROUH,SELL,82574,2,2.83,0.0,0,0,2009-12-17 00:00:00.0,82574,2.83,0,82574,2.83,2009-12-17 06:04:48.0,2009-12-17 06:04:48.0,null,null,null,0,0,'N', 'N',SYSDATE,SYSDATE)

Any help would be highly appreciated

Thanks

What does "Hold Created Inspite of Insufficient Bal" mean? Are you sure that is correct? — Kajetan Abt
– Kajetan Abt, Commented Dec 2, 2010 at 10:29
Thats just a string. when it comes from java it comes quoted. — AnithaR
– AnithaR, Commented Dec 2, 2010 at 12:23

user330315 · Accepted Answer · 2010-12-02 10:36:44Z

2

2009-12-17 06:01:54.0 is not a valid timestamp literal for Oracle

You need to use: TIMESTAMP '2009-12-17 06:01:54.0'

Although it's surprising that one of those statements actually works

answered Dec 2, 2010 at 10:36

user330315

Sign up to request clarification or add additional context in comments.

6 Comments

AnithaR Over a year ago

Hundreds of such insert and update statements work. but once in a while say in one month 2 or 3 transactions fail. So i hope the format cannot be the problem. Is that something else i am missing you will help me to dig it out

user330315 Over a year ago

then you are not showing us the real SQL. The above statements will fail on Oracle

AnithaR Over a year ago

But my sql is run as prepared statement. How do i get the actual query that is sent to the database

user330315 Over a year ago

How do you set the value for the timestamp column in your prepared statement? What is the class of the object instance that you pass to the setXXX() method?

AnithaR Over a year ago

This is the sample : tempPreparedStatement.setObject(11, new java.sql.Timestamp(localTime.getTime()); setTimeStamp is also used

|

Flavio · Accepted Answer · 2010-12-09 09:22:02Z

0

From what you said, two possibilities come to my mind:

You have a concurrency problem. Are you using a shared prepared statement, or a shared connection, accessed by multiple threads concurrently?
You found a bug in the Oracle JDBC driver... did you try updating to the newest version?

answered Dec 9, 2010 at 9:22

Flavio

12k3 gold badges37 silver badges41 bronze badges

Collectives™ on Stack Overflow

ORA-01858 Oracle Date Problem while inserting from java

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related