java.sql.SQLDataException: ORA-01861: 文本与格式字符串 ORA-06512: 在第

Question

我有以下过程，其中包含日期的硬编码值。截至目前尚未使用 bill_ent 变量。

create or replace
PROCEDURE "CUST_S_PAS_GET_SUMM_BE" (bill_ent in varchar2, v_start_dt in date, v_end_dt in date, pasOutput out CUST_TYPES.cursor_type)
is 
BEGIN open pasOutput for
  select
   pa.bill_ent_name as Billing_Entity
  ,count(pa.encounter_id) as Total_Reg
  from cust_s_pas_activity pa
  where pa.reg_dt_tm between to_date('01-01-2018','dd-mm-yyyy') and to_date('15-01-2018','dd-mm-yyyy')
  group by pa.bill_ent_name
  order by pa.bill_ent_name;
END;

此代码在 SQL 开发人员中工作正常。但是，当我从 java 尝试它时，它会抛出错误。

爪哇代码 -

String prepSP = "{call CUST_S_PAS_GET_SUMM_BE(?,?,?,?)}";
objDBSqlStmt = objDBConn.prepareCall(prepSP);
objDBSqlStmt.setString(1, be_list);
 objDBSqlStmt.setString(2, sDate);
 objDBSqlStmt.setString(3, eDate);
 objDBSqlStmt.registerOutParameter(4, OracleTypes.CURSOR);
  objDBSqlStmt.executeQuery();

错误是：

java.sql.SQLDataException: ORA-01861: literal does not match format string
ORA-06512: at line 1
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at MDJavaProgram.MDDBUtility.getPASSummaryByBE(MDDBUtility.java:11366)
    at MDServLet.MDSPASHome.doGet(MDSPASHome.java:77)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:620)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)

Answer 1

使用objDBSqlStmt.setDate(2, java.sql.Date.valueOf(sDate)); and objDBSqlStmt.setDate(3, java.sql.Date.valueOf(eDate));而不是objDBSqlStmt.setString(2, sDate);尝试在日期中设置字符串。