oracle错误(四) ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错的解决办法

于 2015-06-08 15:07:08 发布
阅读量3.1w 收藏 2
点赞数
分类专栏： 数据库-oracle 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zengdeqing2012/article/details/46412863
版权

创建一个新表后,插入第一条新数据时,报错信息如下:

2015-06-08 14:51:45,956 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] - Returning JDBC Connection to DataSource
org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

; uncategorized SQLException for SQL []; SQL state [72000]; error code [8002]; ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错
; nested exception is java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
	at com.sun.proxy.$Proxy8.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
	at com.sun.proxy.$Proxy36.insertCmsChannel(Unknown Source)
	at xyx.dsw.business.admin.cmschannel.CmsChannelInfo.onAdd(CmsChannelInfo.java:327)
	at xyx.dsw.business.base.BaseBusiness.add(BaseBusiness.java:67)
	at cn.xyx.dsw.admin.cmschannel.CmsChannelController.saveJson(CmsChannelController.java:141)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2927)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:94)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)
	at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
	at com.sun.proxy.$Proxy20.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
	at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:32)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
	... 44 more
2015-06-08 14:51:45,958 ERROR [cn.xyx.dsw.admin.cmschannel.CmsChannelController] - add save
org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

; uncategorized SQLException for SQL []; SQL state [72000]; error code [8002]; ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错
; nested exception is java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
	at com.sun.proxy.$Proxy8.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:231)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
	at com.sun.proxy.$Proxy36.insertCmsChannel(Unknown Source)
	at xyx.dsw.business.admin.cmschannel.CmsChannelInfo.onAdd(CmsChannelInfo.java:327)
	at xyx.dsw.business.base.BaseBusiness.add(BaseBusiness.java:67)
	at cn.xyx.dsw.admin.cmschannel.CmsChannelController.saveJson(CmsChannelController.java:141)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:326)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: java.sql.SQLException: ORA-08002: 序列 CMS_CHANNEL_SEQ.CURRVAL 尚未在此会话中定义
ORA-06512: 在 "SL.CMS_CHANNEL_TRI", line 2
ORA-04088: 触发器 'SL.CMS_CHANNEL_TRI' 执行过程中出错

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:966)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2927)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:94)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:476)
	at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:45)
	at com.sun.proxy.$Proxy20.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:22)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:51)
	at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:32)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:88)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:43)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:121)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:110)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
	... 44 more

二 解决办法:

之前的创建触发器SQL脚本:

--Create trigger
create or replace trigger CMS_CHANNEL_TRI
  before insert on CMS_CHANNEL  
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
begin
    SELECT CMS_CHANNEL_SEQ.CURRVAL INTO :NEW.ID FROM DUAL;
end;

优化后的SQL脚本: 

--Create trigger
create or replace trigger CMS_CHANNEL_TRI
  before insert on CMS_CHANNEL  
REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
begin
    SELECT CMS_CHANNEL_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
end;




评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值