mysql 迁移到 oracle后mybatis 不支持多条语句同时执行错误信息 ORA-00911: 无效字符

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/oMengQiQi1/article/details/90746694

之前项目mysql mybatis delete为:(mysql连接中添加allowMultiQueries=true)

<delete id="xxx" parameterType="map">
        delete from A where USER_ID = #{userId};
        delete from B where USER_ID = #{userId};
        delete from C where USER_ID = #{userId};
        delete from D where USER_ID = #{userId};
</delete>

ORA-00911: 无效字符,其实提示的是第一条sql语句的分号是无效字符,因为oracle把begin end 之间的作为一条SQL来执行。

现在oracle mybatis delete 中添加 begin end

<delete id="xxx" parameterType="map">
        begin
	        delete from A where USER_ID = #{userId};
	        delete from B where USER_ID = #{userId};
	        delete from C where USER_ID = #{userId};
	        delete from D where USER_ID = #{userId};
        end;
</delete>
展开阅读全文

急:Java调用Oracle执行SQL:ORA-00911: 无效字符

12-24

开发语言:Javarn数据库:Oracle 10grn问题:在Java代码中,有三个SQL需要执行,1个是INSERT,1个是UPDATE,另一个是DELETE,我使用分号(;)将三个SQL连接起来,然后将其执行rn[code=Java]con.prepareStatement(allSQL.toString()).execute();[/code]rn但会报如下错误:rn[code=Java]rnjava.sql.SQLException: ORA-00911: 无效字符rnrn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)rn at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)rn at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)rn at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)rn at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)rn at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)rn at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)rn at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)rn at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)rn at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)rn at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)rn at com.ebest.pg.dao.VisitDao.addVisitMsg(VisitDao.java:452)rn at com.ebest.pg.service.VisitMsgService.addVisitMsg_tran(VisitMsgService.java:222)rn at com.ebest.pg.service.VisitMsgService.doService(VisitMsgService.java:25)rn at com.ebest.pg.servlet.ServletApp.doService(ServletApp.java:161)rn at com.ebest.pg.servlet.ServletApp.doPost(ServletApp.java:72)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)rn at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)rn at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)rn at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)rn at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)rn at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)rn at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)rn at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)rn at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)rn at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)rn at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)rn at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)rn at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)rn at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)rn at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)rn at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)rn at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)rn at java.lang.Thread.run(Thread.java:595)rn[/code]rnrnrn我保证每个SQL语句都能单独在SQL Plus中正常执行的。 论坛

Caused by: java.sql.SQLException: ORA-00911: 无效字符

05-26

if(stat)n sqlsb.append(" select * from (");n elsen sqlsb.append("select count(*) from (");n n sqlsb.append("select bb.datetime datetime, sum(decode(bb.typevalue, '新入金', bb.inits, 0.0)) as inits1, " +n " sum(decode(bb.typevalue, '还款本金', bb.inits, 0.0)) as inits2, " +n " sum(decode(bb.typevalue, '赎回金额', bb.inits, 0.0)) as inits3, " +n " sum(decode(bb.typevalue, '借款金额', bb.inits, 0.0)) as inits4, " +n " sum(decode(bb.typevalue, '历史空闲资金', bb.inits, 0.0)) as inits5 from (select to_char(t.start_date, 'yyyy-MM-dd') datetime, " +n " sum(t.amount) inits, '新入金' typevalue from p2p_investment_capitalinfo t where t.type = 02 " +n " and t.status in (01, 02, 03) group by to_char(t.start_date, 'yyyy-MM-dd') " +n " union all select to_char(t.create_date, 'yyyy-MM-dd') datetime, sum(t.split_capital) inits, " +n " '还款本金' typevalue from clspuser.crf_p2p_capital_split_main t where t.capital_type = 1 " +n " group by to_char(t.create_date, 'yyyy-MM-dd') union all select to_char(t.pay_date, 'yyyy-MM-dd') datetime," +n " sum(t.amount) inits, '赎回金额' typevalue from p2p_investment_payinfodetail t where t.is_transfer is null" +n " group by to_char(t.pay_date, 'yyyy-MM-dd') union all select to_char(t.match_date, 'yyyy-MM-dd') datetime," +n " sum(t.amount) inits, '借款金额' typevalue from p2p_investment_capital_debt t where t.status != 06" +n " and t.redempt_number is null group by to_char(t.match_date, 'yyyy-MM-dd') union all select to_char(t.start_date, 'yyyy-MM-dd') datetime," +n " sum(t.amount - nvl(t.handled_amount, 0)) inits, '历史空闲资金' typevalue from p2p_investment_capitalinfo t where t.pri_number != 247478" +n " and t.status in (01, 02) and t.type = 01 and t.investor_number != 165951 group by to_char(t.start_date, 'yyyy-MM-dd'))bb where 1=1" );n //创建时间段查询n if(null !=po.getStartAppDate())n sqlsb.append(" and bb.datetime >= '"+po.getStartAppDate()+"'");n n if( null != po.getEndAppDate()) n sqlsb.append(" and bb.datetime <= '"+po.getEndAppDate()+"'");n n sqlsb.append(" group by bb.datetime order by bb.datetime desc);");n n n n n String sqlStr = this.getInternalAccountMoneyWarningsql(po,false);n SQLQuery sq = this.getSession().createSQLQuery(sqlStr);n 在oracle没有错 但是hibernate 执行sql出现Caused by: java.sql.SQLException: ORA-00911: 无效字符 问答

没有更多推荐了,返回首页