Fire using spring SimpleJdbcCall to call Oracle function

Question

I am struggling with the code below to make it work, look for documentation and forums, and get stuck. Finally, I decided to ask you for help. I have a package with TYPES, FUNCTION declarations and a FUNCTION BODY declaration. In the future I would like to use SYNONYM for MYPACKAGE (this is just a layout - I will not have package and type declarations in my database, but use dblink for the external database and Java code to run the procedures / functions, but now I do not have this available dblink), and MYPACKAGE will be available through dblink:

create public synonym dblink_MYPACKAGE for SOME_SCHEMA.MYPACKAGE@dblink _externalDB; 

and I will use dblink_MYPACKAGE instead of MYPACKAGE in Java Code. (but it 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 ; 

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

The problem is only with the OUT parameter, the same code works when I do not pass the OUT parameter and run it against another version of MYFUNCTION, which does not have the OUT parameter.

I also tried using OracleTypes.ARRAY (wrong name pattern) and OracleTypes.OTHER (called: java.sql.SQLException: wrong column type: 1111)

Answer 1

It seems that you are using the wrong method call: Your code: .withProcedureName ("MyFunction") [..] should be replaced with .withFunctionName [...]

Here are some simple examples of calling entire functions:

 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();