title: Strugging with spring SimpleJdbcCall to call Oracle function
body: <p>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:</p>
  
  <pre><code>create public synonym dblink_MYPACKAGE for SOME_SCHEMA.MYPACKAGE@dblink_externalDB;
  </code></pre>
  
  <p>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...</p>
  
  <pre><code>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&lt;String, Object&gt; 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 ;
  </code></pre>
  
  <p>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</p>
  
  <p>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. </p>
  
  <p>I tried also with OracleTypes.ARRAY (invalid name pattern) and OracleTypes.OTHER (Caused by: java.sql.SQLException: wrong column type: 1111)</p>