为了更快的定位您的问题,请提供以下信息,谢谢
【详述】我在测试通过jdbc catalog的方式,来查询oracle中的数据。简单的select可以查询返回结果,但是给查询添加了日期条件后,查询就会报错。
同样的查询条件,我还测试了查询mysql,它是可以正常返回查询结果的。
【背景】
【业务影响】
【是否存算分离】否
【StarRocks版本】3.4.0
【集群规模】单机版
【机器信息】
【联系方式】ouqiubin@163.com
【附件】
----- 创建oracle的jdbc catalog
CREATE EXTERNAL CATALOG jdbc1
PROPERTIES
(
“type”=“jdbc”,
“user”=“xxxxxx”,
“password”=“123456”,
“jdbc_uri”=“jdbc:oracle:thin:@192.168.110.1:1521:rhis”,
“driver_url”=“file:///opt/driver/ojdbc8-23.2.0.0.jar”,
“driver_class”=“oracle.jdbc.driver.OracleDriver”
);
------ 带日期条件的查询语句
查询语句1:
select * from jdbc1.xxxxxx.DWD_D_TEST
where SDATE > STR_TO_DATE(‘2021-01-01 00:00:56’, ‘%Y-%m-%d %H:%i:%s’)
-
查询语句1的报错:
SQL 错误 [1064] [42000]: open JDBCScanner failed, error: java.sql.SQLDataException: ORA-01861: literal does not match format stringat oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:540)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:256)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:253)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:86)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:765)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:921)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1099)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1384)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3687)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at com.starrocks.jdbcbridge.JDBCScanner.open(JDBCScanner.java:95)
Caused by: Error : 1861, Position : 107, Sql = SELECT * FROM (SELECT ORD, SDATE, USERID, UNAME FROM “USERMCHIS_TJSFEZX210616”.“DWD_D_TEST”
WHERE (SDATE > ‘2025-01-03 00:00:56’)
)
WHERE ROWNUM <= 200,
OriginalSql = SELECT * FROM (SELECT ORD, SDATE, USERID, UNAME FROM “USERMCHIS_TJSFEZX210616”.“DWD_D_TEST” WHERE (SDATE > ‘2025-01-03 00:00:56’))
WHERE ROWNUM <= 200, Error Msg = ORA-01861: literal does not match format string
查询语句2:
select * from jdbc1.xxxxxx.DWD_D_TEST
where SDATE > to_date(‘2025-01-03 00:00:56’, ‘YYYY-MM-DD HH24:MI:SS’)
查询语句2的报错:
SQL 错误 [1064] [42000]: Getting analyzing error from line 2, column 14 to line 2, column 68. Detail message: No matching function with signature: to_date(varchar, varchar).