与spring SimpleJdbcCall斗争以调用Oracle函数 [英] Strugging with spring SimpleJdbcCall to call Oracle function

问题描述

我正在努力使用下面的代码来使其运行，搜索文档和论坛而陷入困境. 最后，我决定向您寻求帮助. 我所拥有的是带有TYPES，FUNCTION声明和FUNCTION BODY声明的程序包. 将来我想使用SYNONYM来MYPACKAGE(这只是模拟的-我的数据库中没有包和类型的声明，但是使用dblink到外部数据库和Java代码来运行过程/函数，但是现在我没有此dblink可以访问)和MYPACKAGE可以通过dblink进行访问:

I am struggling with the below code to make it work, searching documentation and forums and stucked. Finally I decided to ask you for help. What I have is package with TYPES, FUNCTION declarations and FUNCTION BODY declaration. In future I would like to use SYNONYM to MYPACKAGE (This is only mock - I will not have package and types declarations in my database, but use dblink to external database and Java code to run procedures / functions, but now I don't have this dblink accessible) and MYPACKAGE will be something accessible through dblink:

create public synonym dblink_MYPACKAGE for SOME_SCHEMA.MYPACKAGE@dblink_externalDB;

，我将在Java代码中使用dblink_MYPACKAGE而不是MYPACKAGE. (但这没关系吗?)外部数据库不是我们的，所以我们无法在此进行任何更改...

and I will be using dblink_MYPACKAGE instead of MYPACKAGE in Java Code. (but this doesn't matter does it?) The external database is not ours, so we CAN'T change anything there...

public class TestClassSpringBased {

private DataSource dataSource;

private SimpleJdbcCall jdbcCall;

@Override
public void testMe(Integer id) {

    int iid = 1;
    SqlParameterSource in = new MapSqlParameterSource().addValue("IN_1", iid);

    Map<String, Object> out = jdbcCall.execute(in);

}

public DataSource getDataSource() {
    return dataSource;
}

public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.setResultsMapCaseInsensitive(true);

    this.jdbcCall = new SimpleJdbcCall(dataSource)
            .withCatalogName("MYPACKAGE")
            .withProcedureName("MYFUNCTION")
            .withReturnValue()
            .useInParameterNames("IN_1")
            .declareParameters(
                    new SqlInOutParameter("IN_1", OracleTypes.NUMBER),
                    new SqlInOutParameter("OUT_1", OracleTypes.STRUCT, "MYPACKAGE.CUSTOMELEMENTSTYPE",
                            new SqlReturnType() {
                                public Object getTypeValue(CallableStatement cs, int colIndx, int sqlType,
                                        String typeName) throws SQLException {

                                    return null; //just let it work, the I will think what to write here
                                }
                            }));

}

}





 create or replace 
 PACKAGE         MYPACKAGE IS 


   TYPE CUSTOMELEMENTSTYPE_R IS RECORD (
     C1 VARCHAR2(60),   
     C2    VARCHAR2(30)

  );

  TYPE CUSTOMELEMENTSTYPE IS TABLE OF CUSTOMELEMENTSTYPE_R 
  INDEX BY PLS_INTEGER;



 FUNCTION MYFUNCTION(
   IN_1 IN INTEGER, OUT_1 OUT CUSTOMELEMENTSTYPE )
 RETURN VARCHAR2;


   END;



create or replace 
 PACKAGE BODY MYPACKAGE  IS

   FUNCTION MYFUNCTION(
     IN_1 IN INTEGER, OUT_1 OUT CUSTOMELEMENTSTYPE )
    RETURN VARCHAR2  IS

  BEGIN

 SELECT *  BULK COLLECT INTO OUT_1
    FROM  SOME_TABLE;
   RETURN 'return param';
 END MYFUNCTION; 

  END MYPACKAGE ;

错误是: org.springframework.jdbc.UncategorizedSQLException:CallableStatementCallback;未分类的SQLException for SQL [{? =致电MYPACKAGE.MYFUNCTION(?,?)}]； SQL状态[99999];错误代码[17074]；无效的名称模式:MYPACKAGE.CUSTOMELEMENTSTYPE;嵌套的异常是java.sql.SQLException:无效的名称模式:MYPACKAGE.CUSTOMELEMENTSTYPE

The ERROR is: org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call MYPACKAGE.MYFUNCTION(?, ?)}]; SQL state [99999]; error code [17074]; invalid name pattern: MYPACKAGE.CUSTOMELEMENTSTYPE; nested exception is java.sql.SQLException: invalid name pattern: MYPACKAGE.CUSTOMELEMENTSTYPE

问题仅在于OUT参数，当我不传递OUT参数并针对没有OUT参数的另一个版本的MYFUNCTION运行它时，相同的代码也起作用.

The problem is only with OUT parameter, the same code works, when I dont pass OUT parameter and run it against another version of MYFUNCTION, that has not OUT parameter.

我也尝试使用OracleTypes.ARRAY(无效的名称模式)和OracleTypes.OTHER(由:java.sql.SQLException:错误的列类型:1111引起)

I tried also with OracleTypes.ARRAY (invalid name pattern) and OracleTypes.OTHER (Caused by: java.sql.SQLException: wrong column type: 1111)

推荐答案

似乎您使用了错误的方法调用: 您的代码: .withProcedureName("MYFUNCTION")[..] 应该替换为 .withFunctionName [...]

It seems that You use incorrect method call: Your code: .withProcedureName("MYFUNCTION")[..] should be replaced by .withFunctionName[...]

这是整个函数调用的一些简单示例:

here is some simple examle of whole function call:

JdbcTemplate jdbc = new JdbcTemplate(txManager.getDataSource());
    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbc)
            .withCatalogName("p_adm_www")
            .withFunctionName("fn_usr_get_login_sequence")
            .declareParameters(new SqlOutParameter("RETURN", OracleTypes.NUMBER))
            .withoutProcedureColumnMetaDataAccess();
    jdbcCall.setAccessCallParameterMetaData(false);
    BigDecimal returnId = jdbcCall.executeFunction(BigDecimal.class, null);
    return returnId.longValue();

这篇关于与spring SimpleJdbcCall斗争以调用Oracle函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！