apache / apache/incubator-seata

orcale update 语句报错java.sql.SQLSyntaxErrorException: ORA-00904

Open
#2,854 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
26k
Forks
8.8k
Avg merge
1d 8h
Merged PRs (30d)
4

Description

### Ⅰ. Issue Description
执行update语句报错

### Ⅱ. Describe what happened
update 语句执行报错,未使用DataSourceProxy时可以正常执行

driverClassName配置

spring:
datasource:
ddms:
driverClassName: oracle.jdbc.driver.OracleDriver
jdbc-url: jdbc:oracle:thin:@//200.100.100.8:1521/orcl
username: wdp
password: wdp
max-idle: 8
min-idle: 8
max-active: 20
initial-size: 10
max-maxWait: 100
testOnBorrow: true
validationQuery: select 1;
testWhileIdle: true

DataSource配置

@Bean
@ConfigurationProperties(prefix = "spring.datasource.ddms")
public DataSource druidDataSource(){

return DataSourceBuilder.create().build();
}

@Primary
@Bean("dataSource")
public DataSourceProxy dataSource(DataSource dataSource){
return new DataSourceProxy(dataSource);
}

@Bean(name = "sqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSourceProxy dataSourceProxy,
@Qualifier("configuration") org.apache.ibatis.session.Configuration config) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSourceProxy);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:com/**/ddms/*/*/*.xml"));
bean.setConfiguration(config);

//添加PageHelper插件
Interceptor interceptor = new PageInterceptor();
Properties properties = new Properties();
//数据库类型
properties.setProperty("helperDialect", "oracle");

//是否将参数offset作为PageNum使用
properties.setProperty("offsetAsPageNum", "true");
//是否进行count查询
properties.setProperty("rowBoundsWithCount", "true");
//是否分页合理化
properties.setProperty("reasonable", "false");
interceptor.setProperties(properties);

//添加cat插件
Interceptor catMybatisPlugin = new CatMybatisPlugin();
bean.setPlugins(new Interceptor[] {interceptor, catMybatisPlugin});

return bean.getObject();
}

@Bean(name = "transactionManager")
@Primary
public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
//public DataSourceTransactionManager transactionManager(DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}

@Bean(name = "sqlSessionTemplate")
@Primary
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}

1>错误1
### SQL: SELECT item_id, item_code, item_cnname, item_cnalias, item_enname, item_enalias, cnname_memory_code, alias_memory_code, input_code1, input_code2, drug_standard_code as item_standard_code, classifi_id, dosage_form_id, item_priv_id, item_type, drug_spec as item_spec, pkg_unit, pkg_unit_ratio, st_profit_rate as profit_rate, branch_id, buy_price, sale_price, min_pkg_unit, manufacturer_code, manufacturer_name FROM VSTC_DRUG WHERE 1 = 1 AND BRANCH_ID = ? AND ( ITEM_CODE LIKE '%`1%' ESCAPE '/' OR ITEM_CNNAME LIKE '%`1%' ESCAPE '/' OR UPPER(CNNAME_MEMORY_CODE) LIKE '%UPPER(`1%)' ESCAPE '/' OR UPPER(ALIAS_MEMORY_CODE) LIKE '%UPPER(`1%)' ESCAPE '/' ) AND ITEM_PRIV_ID IN ( ? , ? , ? , ? )
### Cause: java.sql.SQLSyntaxErrorException: ORA-00904: "ITEM_CODEIKE": 标识符无效

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ITEM_CODEIKE": 标识符无效

ITEM_CODE LIKE 变成ITEM_CODEIKE
2>、错误2
### SQL: update STB_STOCK SET EXPORT_QUANTITY=?, EXPORT_MIN_QUANTITY=?, EXPORT_SALE_AMOUNT=?, STOCK_QUANTITY=?, STOCK_MIN_QUANTITY=?, STOCK_SALE_AMOUNT=?, UPDATE_USER=?, UPDATE_DATE = sysdate, VERSION_NUMBER = VERSION_NUMBER + 1 where IMPORT_DETAIL_ID= ? and IS_AVAILABLE='Y' and VERSION_NUMBER = ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00904: "ERSION_NUMBERHERE": 标识符无效

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ERSION_NUMBERHERE": 标识符无效
```
可能是VERSION_NUMBER + 1 where变成ERSION_NUMBERHERE
```

### Ⅲ. Environment:

- JDK version :1.8.0_212
- OS :mac os
- Others:
Oracle版本
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
驱动版本
com.oracle
ojdbc7
12.1.0.2

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the reported SELECT and UPDATE with the shown Oracle 10g, ojdbc7, Spring, MyBatis, and DataSourceProxy configuration. Compare the SQL before and after DataSourceProxy processing, focusing on the reported LIKE and VERSION_NUMBER/where mutations. Done means both statements execute without identifier errors through the proxy.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.