Calling A Pl Sql Function With Object Parameter In Java

In Oracle SQL I have a type: CREATE OR REPLACE type address_type AS OBJECT ( Street VARCHAR2(100), Road VARCHAR2(100), Town VARCHAR2(100), County VARCHAR2(100) ); This is used

Solution 1:

Create the type in the anonymous PL/SQL block (rather than in Java) and pass in the values:

CallableStatementst= con.prepareCall("BEGIN ? := ADD_ADDRESS_FUNC( Address_Type(?,?,?,?)); END;");

st.registerOutParameter( 1, Types.INTEGER );
st.setString( 2, "Address 1" );
st.setString( 3, "Address 2" );
st.setString( 4, "Town" );
st.setString( 5, "County" );

st.execute();
intid= st.getInt(1);

The alternative is that you can use JPublisher to generate the Address class so that it can be passed to/from the database.

Solution 2:

For Oracle Functions, you can use the native sql query with Hibernate as such:

Queryquery= session.createSqlQuery("select PACKAGE.AddAdress(:STREET, :ROAD, :TOWN, :COUNTRY) as ADDRESS_ID from DUAL")
             .addScalar("ADDRESS_ID", java.sql.integer);

  query.setParameter(":STREET", "Street name")
  query.setParameter(":ROAD", "Road name")
  etc... ;

intaddrId= (int) query.UniqueResult();

The addScalar method is necessary for retrieving the ResultSet. If your function returns an Oracle defined Type, you can say:

.addScalar("ADDRESS_ID", newCustomType(newOracleType()))

And this OracleType would be the type matching the Oracle Type. So create a class OracleType which implements UserType, and override the method

nullSafeGet(ResultSet rs, Sting name..){
   Object[] result = (java.sql.STRUCT) rs.getObject(names[0]).getAttributes()
}

This result will then be the object returned by query.uniqueResult as such: Object[] result = query.uniqueResult() in your query method.