title: How to call an Oracle PL-SQL procedure which has a custom defined complex Object type as input parameter from Java
body: <p>In Oracle database, I have defined an Object type which has a nested Object type. Assuming, that I have a PL-SQL procedure which uses the parent Object type as input parameter, I'm looking for a performant way to call the procedure from Java code.</p>
  
  <pre><code>CREATE OR REPLACE TYPE STUDENT AS OBJECT ( 
       name         VARCHAR2(35),
       address      HOME_ADDRESS
  );
  
  CREATE OR REPLACE TYPE HOME_ADDRESS AS OBJECT ( 
       street    VARCHAR2(35)
  );
  
  CREATE OR REPLACE PROCEDURE TEST(
      studentObject IN STUDENT
  )
  IS
  BEGIN
       ... do domething ...
  END;
  </code></pre>
  
  <p>On Java side, I have the relevant POJOs holding data.</p>
  
  <p>I have tried using Spring SimpleJdbcCall along with a StructMapper.</p>
  
  <pre><code>StructMapper&lt;Demo&gt; MAPPER = BeanPropertyStructMapper.newInstance(Student.class);
  SqlParameter studentIn = new SqlParameter("studentObject", Types.STRUCT, "MY_SCHEMA.STUDENT");
  SimpleJdbcCall sjc = new SimpleJdbcCall(datasource).withSchemaName("MY_SCHEMA")
        .withProcedureName("TEST")
        .withoutProcedureColumnMetaDataAccess()
        .useInParameterNames("studentObject")
        .declareParameters(studentIn);
  final MapSqlParameterSource inputParameters = new MapSqlParameterSource().addValue("studentObject",
              new SqlStructValue&lt;Student&gt;(studentInstance, MAPPER, "MY_SCHEMA.STUDENT"));
  final Map&lt;String, Object&gt; execute = sjc.execute(inputParameters);
  </code></pre>
  
  <p>The above example works when not using nested Object types. When using a nested Object type, a <code>java.sql.SQLException: Fail to convert to internal representation</code> exception is thrown. Besides that, spring-data-oracle and spring-data-jdbc-ext projects seems to not be maintained anymore and I would like to avoid using them. The above code is not my real code but an example for the question's purposes. My real Object type and the corresponding POJO in Java might be quite complex with many nested objects and arrays.</p>