Calling a PL SQL Function with object parameter in java

Question

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 for a function, the function uses the ADDRESS_TYPE to take an answer as a parameter and returns an integer:

create or replace FUNCTION ADD_ADDRESS_FUNC(
    New_Address IN Address_Type )
  RETURN INTEGER
AS
  AddressID INTEGER;
BEGIN
  AddressID := ADDRESS_SEQ.NEXTVAL;
  INSERT
  INTO Address VALUES
    (
      AddressID,
      New_Address.Street,
      New_Address.Road,
      New_Address.Town,
      New_Address.County
    );
  DBMS_OUTPUT.PUT_LINE(AddressID);
  RETURN AddressID;
END;

In my Java classes I have all connections etc & I can access other procs & functions but not this one which takes the Address object. In Java I create an Address object a with 4 strings, surely I can just pass this as second argument below:

Address a = new Address("Address 1", "Address 2", "Town", "County");
CallableStatement stmt = conn.prepareCall( "begin ? := ADD_ADDRESS_FUNC(?);end;" );
stmt.registerOutParameter(1, Types.CHAR);
stmt.setObject(2, a);
stmt.execute ();
int memberID = stmt.getInt(1);`

The error message says invalid column index and it is breaking down at the setObject call. If it were a string it would be fine but the Address object isn't working. Any help appreciated, thanks in advance

The more recent JDBC (I believe Java 7 and later) have a Struct capability that lets you map Java objects to SQL objects. I've never done this so I'm posting this as a comment instead of an answer since I can't give you a lot of detail. — Jim Garrison
– Jim Garrison, Commented Mar 18, 2016 at 4:25
@JimGarrison, what you refer to is the UserType and CompositeType interfaces that hibernate provides, in which you can parse the STRUCT object/ResultSet object as you wish. It has some methods that gets called when query.listResults() or similar is invoked. — alperc
– alperc, Commented Aug 23, 2018 at 10:50

MT0 · Accepted Answer · 2016-03-18 10:45:03Z

0

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

CallableStatement st = 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();
int id = 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.

edited Mar 18, 2016 at 10:45

answered Mar 17, 2016 at 23:50

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

beehive Over a year ago

Thanks guys, I'm not the smartest at Java so I'll call the type and set the strings as above. Although it should be simple to map a Java object with a PL/SQL type, if I figure in the meantime I will post

alperc Over a year ago

Connection conn ? SessionFactory.getCurrentSession().getConnection().preparecall is deprecated

alperc Over a year ago

instead use: session.doWork(connection->connection.prepareCall(..))

MT0 Over a year ago

@ot0 java.sql.Connection#prepareCall( String sql ) is not documented as being deprecated. Also, there are no SessionFactory or session variables mentioned in my answer or the question so I do not know what framework you are using (although I would guess Hibernate) but it does not seem relevant to my answer or the question which is about pure Java and not any framework.

alperc Over a year ago

@MTO I assumed you were using SessionFactory` of Hibernate. It would be good, if you mentioned also how the con object was retrieved. In Hibernate 5 and =>, the method is deprecated. Just a bonus info for readers then.

alperc · Accepted Answer · 2018-08-24 07:47:59Z

0

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

Query query = 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... ;

int addrId = (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", new CustomType(new OracleType()))

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.

edited Aug 24, 2018 at 7:47

answered Aug 23, 2018 at 10:47

alperc

3832 silver badges18 bronze badges

1 Comment

MT0 Over a year ago

The question is not tagged with Hibernate - you should make it clear in your answer if you are using a framework because the question is not tagged with any and is looking for a pure Java answer unrelated to any frameworks.

Lukas Eder · Accepted Answer · 2022-10-20 09:48:45Z

Doing this with JDBC's `SQLData` SPI

The accepted answer shows a useful workaround where you compose your OBJECT type in an anonymous block. If you want to do this with a single bind variable, as you mentioned in your question, then you could create a class that implements java.sql.SQLData:

class Address implements SQLData {
    // Constructors, getters, and setters

    @Override
    public String getSQLTypeName() throws SQLException {
        return "SCHEMA.ADDRESS_TYPE";
    }

    @Override
    public final void readSQL(SQLInput stream, String typeName)
    throws SQLException {
        this.street = stream.readString();
        this.road = stream.readString();
        // ...
    }

    @Override
    public final void writeSQL(SQLOutput stream)
    throws SQLException {
        stream.writeString(street);
        stream.writeString(road);
        // ...
    }
}

Now, you can set that value to your prepared statement:

stmt.setObject(2, a);

If you want to read it from a ResultSet, you have to register that type in a Map<String, Class<?>>:

Map<String, Class<?>> types = Map.of("SCHEMA.ADDRESS_TYPE", Address.class);
rs.getObject(position, map);

This approach is more viable also when you create collections of ADDRESS_TYPE, as you can then re-use the above class and get Address[] results for your collections.

Using a third party library

Note that jOOQ, a third party library, has a code generator for all of these object types that helps you automate all of this, including calling of procedures, etc. With jOOQ, you'd just write:

AddressRecord a = new AddressRecord("Address 1", "Address 2", "Town", "County");
// configuration contains your JDBC connection
int memberId = Routines.addAddressFunc(configuration, a);

Disclaimer: I work for the company behind jOOQ.

Collectives™ on Stack Overflow

Calling a PL SQL Function with object parameter in java

3 Answers 3

5 Comments

1 Comment

Doing this with JDBC's `SQLData` SPI

Using a third party library

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Doing this with JDBC's SQLData SPI

Using a third party library

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Doing this with JDBC's `SQLData` SPI