Strugging with spring SimpleJdbcCall to call Oracle function
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();
Admin
Updated on June 26, 2022Comments
-
Admin almost 2 years
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;
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 ;
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
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.
I tried also with OracleTypes.ARRAY (invalid name pattern) and OracleTypes.OTHER (Caused by: java.sql.SQLException: wrong column type: 1111)