Srikanth Sridhar Srikanth Sridhar - 14 days ago 4
Java Question

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

When i execute my prepared statement i am getting the

ORA-01858: a non-numeric character was found where a numeric was expected
. When i searched through some forums, they say it is because of the date datatype. My prepared statement is

insert into OPRS_ZONES(
ZONE_ID,
ZONE_CODE,
ZONE_NAME,
PLACE_ID,
CORP_ID,
CREATED_BY,
CREATED_DATE,
MODIFIED_BY,
MODIFIED_DATE)
values(?,?,?,?,?,?,?,?,?)


The values i am trying to insert are

03.0,
'FLORIDA',
'FLORIDA',
05231.0,
01.0,
01.0,
TO_DATE('19102012130639','DDMMYYYYHH24MISS'),
NULL,
NULL


Table Definition

CREATE TABLE OPRS_ZONES (
ZONE_ID NUMERIC(20,0) ,
ZONE_CODE VARCHAR2(16) ,
ZONE_NAME VARCHAR2(255) ,
PLACE_ID NUMERIC(20,0) ,
CORP_ID NUMERIC(20,0) ,
CREATED_BY NUMERIC(20,0) ,
CREATED_DATE DATE ,
MODIFIED_BY NUMERIC(20,0) ,
MODIFIED_DATE DATE );


The code where i am populating the values for prepared statement,

public int executePreparedStatement(String query, List myCollection, int colLength, String tableName) throws DBException,SQLException {

int rowsAffected = 0;
int [] noOfRowsExecuted = null;
try{
conn.setAutoCommit(false);
if(query != null){
ps = conn.prepareStatement(query);
for (int i = 0; i < myCollection.size(); i++) {
logger.info("@@mycollcetion -- "+myCollection.get(i));
List list = (List) myCollection.get(i);
int count = 1;
for (int j = 0; j < list.size(); j++) {
ps.setObject(count, list.get(j));
count++;
}
ps.execute();
logger.info("@@ noOfRowsExecuted == "+noOfRowsExecuted);
}
}
}catch(Exception e){
logger.error("Error in the execution of Prepared Statement: \n" + query + "\nData : " + listData, e);
rowsAffected = Utility.getErrorCode(e);
throw new DBException(e);
}finally{
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (Exception e) {
}
rowsAffected = 0;
noOfRowsExecuted = null;
}
return rowsAffected;
}


The
list myCollection
contains the
values
. The variabele
query
is the
prepared statement

This prepared statement when replaced with values works fine in SQLDEVELOPER, but when i run through my java program i'm getting error. Is this really problem with date format ? if so why is it working fine in SQLDEVELOPER ?

Please help,

Thanks

Answer

Change your prepared statement in:

insert  into OPRS_ZONES(
  ZONE_ID,
  ZONE_CODE,
  ZONE_NAME,
  PLACE_ID,
  CORP_ID,
  CREATED_BY,
  CREATED_DATE,
  MODIFIED_BY,
  MODIFIED_DATE) 
values(?,?,?,?,?,?,
       TO_DATE(?,'DDMMYYYYHH24MISS'),
       ?,?)

And set only the actual CREATED_DATE as a string, that's to say make the getter in your collection object return "19102012130639" and not the whole TO_DATE syntax.

EDIT: assuming you have a String array containing all your values, and you use my edited statement version, this should work.

   String[] values = { "03.0", "FLORIDA", "FLORIDA", "05231.0", "01.0", "01.0",   "19102012130639", null, null };
   ps = conn.prepareStatement(query);
   int i = 1;
   foreach(String par : values) {
      ps.setObject(i, par);
      i++;
   }
   ps.execute();
Comments