使用Spring调用oracle StoredProcedure时出错 [英] Error calling oracle StoredProcedure with Spring
问题描述
我有一个不能更改或编写代码的oracle存储过程.该过程接收以下I/O
I have an oracle Stored Procedure which i can't change or code. The Procedure receives the following I/O
CREATE OR REPLACE PROCEDURE "SCHEMA"."NAMEPROCEDURE"
(
ZCLIENTE_IN IN CHAR,
CKBALCAO_IN IN CHAR,
NRPROCES_IN IN NUMBER,
DTINICIO_IN IN CHAR,
DTFIM_IN IN CHAR,
TPDOCUME_IN IN CHAR,
EMAIL_IN IN CHAR,
OK_KO_OUT OUT CHAR,
MSGERR_OUT OUT VARCHAR2
)
IS
PROXNUM CHAR(14);
BEGIN ...
...
此过程将我的输入插入到某些表中. 在springBoot应用程序中，我尝试使用以下存储库代码来调用它:
This procedure inserts my Input in some tables. In a springBoot application i am trying to call it with the following repository code:
String procedureName = "NAMEPROCEDURE";
String catalogName = "SCHEMA";
String okKo ="";
String msgErro ="";
simpleJdbcCall.withCatalogName(catalogName).withProcedureName(procedureName)
.declareParameters(new SqlOutParameter("OK_KO_OUT", OracleTypes.CHAR), new SqlOutParameter("MSGERR", OracleTypes.VARCHAR),
new SqlInOutParameter("ZCLIENTE_IN", OracleTypes.CHAR),
new SqlInOutParameter("CKBALCAO_IN", OracleTypes.CHAR),
new SqlInOutParameter("NRPROCES_IN", OracleTypes.NUMBER),
new SqlInOutParameter("DTINICIO_IN", OracleTypes.CHAR),
new SqlInOutParameter("DTFIM_IN", OracleTypes.CHAR),
new SqlInOutParameter("TPDOCUME_IN", OracleTypes.CHAR),
new SqlInOutParameter("EMAIL_IN", OracleTypes.CHAR)
);
MapSqlParameterSource inParams = new MapSqlParameterSource();
inParams.addValue("ZCLIENTE_IN", "1111111111", OracleTypes.CHAR);
inParams.addValue("CKBALCAO_IN", "0000", OracleTypes.CHAR);
inParams.addValue("NRPROCES_IN", 20160000001L, OracleTypes.BIGINT);
inParams.addValue("DTINICIO_IN", "2016-01-01", OracleTypes.CHAR);
inParams.addValue("DTFIM_IN", "2019-01-01", OracleTypes.CHAR);
inParams.addValue("TPDOCUME_IN", "0011", OracleTypes.CHAR);
inParams.addValue("EMAIL_IN", "someEmail@gmail.com", OracleTypes.CHAR);
Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(inParams);
但是我收到以下日志错误:
But i am receiving the follwoing log error:
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback;错误的SQL语法[{call SCHEMA.NAMEPROCEDURE()}];嵌套的异常是 java.sql.SQLException:ORA-06550:linha 1，coluna 7:PLS-00306:número 查买达州报刊 'PROCEDURENAME'ORA-06550:linha 1，coluna 7:〜
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call SCHEMA.NAMEPROCEDURE()}]; nested exception is java.sql.SQLException: ORA-06550: linha 1, coluna 7: PLS-00306: número errado ou tipos de argumentos na chamada para 'PROCEDURENAME' ORA-06550: linha 1, coluna 7:~
原因:java.sql.SQLException:ORA-06550:linha 1，coluna 7: PLS-00306:查麦达·帕拉(Numero errado ou tipos de argumentsos na chamada para) 'PROCEDURENAME'ORA-06550:linha 1，coluna 7:PL/SQL: 声明被忽略
Caused by: java.sql.SQLException: ORA-06550: linha 1, coluna 7: PLS-00306: número errado ou tipos de argumentos na chamada para 'PROCEDURENAME' ORA-06550: linha 1, coluna 7: PL/SQL: Statement ignored
〜[ojdbc7-12.1.0.2.jar:12.1.0.2.0]在 oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) 〜[ojdbc7-12.1.0.2.jar:12.1.0.2.0]在 oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) 〜[ojdbc7-12.1.0.2.jar:12.1.0.2.0]在 oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) 〜[ojdbc7-12.1.0.2.jar:12.1.0.2.0]在 oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) 〜[ojdbc7-12.1.0.2.jar:12.1.0.2.0]在 oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) 〜[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
我不知道我在做什么错，因为如果我只用2个参数进行相同的调用并返回一个Cursor，它就可以正常工作.
I have no idea of what am i doing wrong, because if i have the same call with just 2 arguments and returning a Cursor it works perfectly.
预先付款
推荐答案
为遇到相同问题的任何人找到了答案. 在我的Pom.xml中，我具有ojdbc7驱动程序，并且需要具有ojbdc6.继承人的依存关系:
Found the answer for anyone who's having the same problem. In my Pom.xml I had ojdbc7 driver and need to have ojbdc6. Heres de dependency:
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.4</version>
</dependency>
不知道为什么，但是它对我有用. SQL调用函数和oracle之间必须存在一些不兼容.
No idea why but it worked for me. There must be some incompatibility between SQL- call function and oracle.
这篇关于使用Spring调用oracle StoredProcedure时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
