java 错误：ORA-01858：在需要数字的地方发现了非数字字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13005343/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
error : ORA-01858: a non-numeric character was found where a numeric was expected
提问by Srikanth Sridhar
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
当我执行我准备好的语句时，我得到了ORA-01858: a non-numeric character was found where a numeric was expected. 当我搜索一些论坛时，他们说这是因为日期数据类型。我准备好的声明是
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 myCollectioncontains the values. The variabele queryis the prepared statementThis 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 ?
该list myCollection包含values。变量query是prepared statement当用值替换时这个准备好的语句在 SQLDEVELOPER 中工作正常，但是当我运行我的 java 程序时，我收到错误。这真的是日期格式的问题吗？如果是这样，为什么它在 SQLDEVELOPER 中工作正常？
Please help,
请帮忙，
Thanks
谢谢
回答by BigMike
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.
并且只将实际的 CREATED_DATE 设置为字符串，也就是说让集合对象中的 getter 返回“19102012130639”而不是整个 TO_DATE 语法。
EDIT: assuming you have a String array containing all your values, and you use my edited statement version, this should work.
编辑：假设您有一个包含所有值的 String 数组，并且您使用我编辑过的语句版本，这应该可以工作。
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();
回答by johanwannheden
I encountered the same problem, however, in my case, I had provided sysdateas an argument to the DATEfield of the target table.
我遇到了同样的问题，但是，就我而言，我已将其sysdate作为参数提供给DATE目标表的字段。
The solution was to change the value to a java.sql.Timestamp.
解决方案是将值更改为 a java.sql.Timestamp。
