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

Question

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 1

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();