java 执行oracle存储过程报错:存储过程对象无效 但在oracle客户端里是可以执行的?

报错:

20180202060000.493 [pool-1-thread-1] ERROR c.s.smz.service.CdrStatisticService - 存储过程执行失败:
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [{call smzStatistics()}]; nested exception is java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列: 
PLS-00905: 对象 BUSYCALL.SMZSTATISTICS 无效
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:443)
    at com.sanss.smz.db.Oracle.SmzStCallDao.execSmzStPro(SmzStCallDao.java:73)
    at com.sanss.smz.db.Oracle.SmzStCallDao$$FastClassBySpringCGLIB$$c9e795bc.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at com.sanss.smz.db.Oracle.SmzStCallDao$$EnhancerBySpringCGLIB$$a6fbff68.execSmzStPro(<generated>)
    at com.sanss.smz.service.CdrStatisticService.addCallBackSt(CdrStatisticService.java:225)
    at com.sanss.smz.service.CdrStatisticService.stYesterdayCdr(CdrStatisticService.java:213)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列: 
PLS-00905: 对象 BUSYCALL.SMZSTATISTICS 无效
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
    at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:435)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
    ... 26 common frames omitted
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [{call smzStatistics()}]; nested exception is java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列: 
PLS-00905: 对象 BUSYCALL.SMZSTATISTICS 无效
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:443)
    at com.sanss.smz.db.Oracle.SmzStCallDao.execSmzStPro(SmzStCallDao.java:73)
    at com.sanss.smz.db.Oracle.SmzStCallDao$$FastClassBySpringCGLIB$$c9e795bc.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at com.sanss.smz.db.Oracle.SmzStCallDao$$EnhancerBySpringCGLIB$$a6fbff68.execSmzStPro(<generated>)
    at com.sanss.smz.service.CdrStatisticService.addCallBackSt(CdrStatisticService.java:225)
    at com.sanss.smz.service.CdrStatisticService.stYesterdayCdr(CdrStatisticService.java:213)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列: 
PLS-00905: 对象 BUSYCALL.SMZSTATISTICS 无效
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1033)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
    at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:435)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
    ... 26 more


使用spring框架,jdbc执行:

public void execSmzStPro(){

    String sql = "{call SMZSTATISTICS()}";
    jdbcTemplateOra.execute(sql);

}

存储过程:

CREATE OR REPLACE PROCEDURE SMZSTATISTICS AS
    CURSOR cur_smzcdr_st IS
    select substr(cdrTime,0,10) as sthour, count(STBACK) as stbknum
    from T_CDR_OUTCALL
    where substr(CDRTIME,0,8)='20150318'
    group by substr(cdrTime,0,10)
    order by sthour;
BEGIN
    FOR rec IN cur_smzcdr_st LOOP
      insert into SMZ_ST_CALL(sthour,CALLBKNUM)values(rec.sthour,rec.stbknum);
    END LOOP;
END;

在sql客户端直接执行该存储过程是没问题。
如果游标里面不带where子句,java那边可以执行。

阅读 1.9k
评论
    0 个回答
    撰写回答

    登录后参与交流、获取后续更新提醒

    相似问题
    推荐文章