java - 从 spring boot simpleJDBCcall 调用 PL SQL 过程时出错

Question

我是 Spring boot 开发和 JAVA 新手，请求您的帮助，因为我尝试了从 stackoverflow 获得的所有建议，但没有一个对我有用。

我正在尝试使用简单的 JDBCcall 从 Spring Boot 应用程序调用 Oracle PLSQL 过程。

但是我遇到了如下错误:

java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'WELCOME_MSG1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3857) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374) ~[oracle-jdbc-11.1.0.7.0.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1138) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1135) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1083) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1135) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:405) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:365) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198) ~[spring-jdbc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at com.mud.listcontractsretriever.testproc.TestMain.testClass(TestMain.java:49) ~[classes/:na]
    at com.mud.listcontractsretriever.controller.ListContractsRetrieverRestController.postMethodIsInlandPricing1(ListContractsRetrieverRestController.java:35) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_80]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_80]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_80]
    at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_80]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-4.3.2.RELEASE.jar:4.3.2.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:522) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:1110) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:785) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1425) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [na:1.7.0_80]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [na:1.7.0_80]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.4.jar:8.5.4]
    at java.lang.Thread.run(Thread.java:745) [na:1.7.0_80]

PLSQL过程代码:

CREATE OR REPLACE PROCEDURE welcome_msg1 (p_name IN VARCHAR2,message OUT VARCHAR2) 
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
message:= p_name;
END;
/

Spring 启动类:

package com.mud.listcontractsretriever.testproc;

import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.OracleTypes;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Service;

@Service
public class TestMain {
    @Autowired
    private DataSource datasource;

    private SimpleJdbcCall simpleJdbcCall;
    private JdbcTemplate jdbcTemplate;


    public DataSource getDatasource() {
        return datasource;
    }

    public void setDatasource(DataSource datasource) {
        this.datasource = datasource;
        this.jdbcTemplate =new JdbcTemplate(datasource);
        this.simpleJdbcCall = new SimpleJdbcCall(this.jdbcTemplate);
    }

    public SimpleJdbcCall getSimpleJdbcCall() {
        return simpleJdbcCall;
    }

    public void testClass(String name){
        jdbcTemplate =new JdbcTemplate(datasource);
        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate);

        simpleJdbcCall.withProcedureName("WELCOME_MSG1");
        simpleJdbcCall.addDeclaredParameter(new SqlParameter("p_name", OracleTypes.VARCHAR));
        simpleJdbcCall.withoutProcedureColumnMetaDataAccess();
        SqlParameterSource in= new MapSqlParameterSource().addValue("p_name", name);
        Map<String, Object> out = simpleJdbcCall.execute(in);   
    }
}

请帮忙。

谢谢

Answer 1

尝试向用户授予数据库的权限，例如:

grant execute on (packageName or tableName) to user;