Executing an oracle stored procedure with oracle object types

Hibernate ORM
1

I am trying to call an oracle stored procedure with some user defined Oracle types from a java web service.
I am using Hibernate 6.5 and Java 21 and spring 6.1.
I have tried the approach with the embeddable structure found here in the forum but with no luck.

my stored procedure:

PROCEDURE F_GET_COUNTRY(ctry IN OUT OT_COUNTRY, user_id IN CHAR)

I have prepared a Java record

package entities.Country;

@Embeddable
@Struct(name="OT_COUNTRY")
public record Country(String isoCode, String name, Integer amountOfHabitants) {
}

When calling the stored procedure

public Object getCountry(final Country form, final String user) {
        Session session = getEntityManager().unwrap(Session.class);
        StoredProcedureQuery procedure = session.createStoredProcedureCall("F_GET_COUNTRY", Country.class);
        procedure.registerStoredProcedureParameter("ctry", Country.class, ParameterMode.INOUT);
        procedure.registerStoredProcedureParameter("user_id", String.class, ParameterMode.IN);

        procedure.setParameter("ctry", form);
        procedure.setParameter("user_id", user);

        procedure.execute();
        Object output = procedure.getOutputParameterValue(1);

I always get the Exception:

JdbcTypeRecommendationException: Could not determine recommended JdbcType for Java type 'entities.Country'

Am I missing something?

Regards
Edmund

2

Do you use this embeddable type Country anywhere else in an entity? If not, then I suppose that is the reason why Hibernate ORM doesn’t build a runtime metamodel for this and hence fails with this exception.
For the sake of testing, can you try defining an entity e.g.

@Entity
@Subselect("")
public class EmbeddableHolder {
  @Id String id;
  @Embedded Country country;
}
3

Thanks for your fast answer, but unfortunately, in my Country OT is a list of contact person objects
and for that, I receive
CountryDummy.country.contact_person_List’ is mapped as a basic aggregate component array, but this is not yet supported.
The list contains person objects.

Is it possible to map an OT like this?

Background:
The overall goal of this project is to migrate an existing old Java application which uses stored procedures heavily onto a new modern architecture, without changing the use of the stored procedures now. This is planned for a later step. Today, the stored procedures are called via oracle.jdbc.ObjectType.STRUCT, but this is no longer supported and causes security vulnerabilities. Now we are looking for a new way to use the stored procedures from Java.

Regards
Edmund

4

Hibernate ORM 6.5 is not supported any longer, so you will have to update to 6.6 or even better, 7.0 which AFAIU do support that kind of mapping.

5

Hi,
I followed your suggestions:
I am using hibernate-7.0.6.Final
and the current spring-data-jpa implementation.

I have a Oracle DataType

create or replace TYPE            "OT_COUNTRYKEY" as object
(
 country_code          CHAR(2),
 country_name          VARCHAR2(200),
 amount        NUMBER
)

and a stored procedure:

PROCEDURE X_TEST (xform IN OUT OT_COUNTRYKEY)
   IS
   tform VARCHAR2(20);
   BEGIN
   xform.country_code:='DE';
   xform.country_name:='Germany';
   xform.amount := 3000000;
   END;

The Oracle Custom datatype is defined in a Java Class

@AllArgsConstructor
@Data
@Embeddable
@EqualsAndHashCode
@NoArgsConstructor
@Struct(name = "OT_COUNTRYKEY")
public class OTCountryKey implements Serializable {
    private static final long serialVersionUID = -6577203874286836224L;
    private String countryCode;
    private String countryName;
    private Long amount;
}

The Embeddable is added to a dummy Entity class

@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
@Cacheable
@Data
@Entity
@EqualsAndHashCode
@NoArgsConstructor
@Subselect("")
public class EmbeddableHolder implements Serializable {

    private static final long serialVersionUID = 4076031794180993729L;
    @Id
    private String Id;
    @Embedded
    private OTCountryKey otCountryKey;
}

The call of the stored procedure looks like this:

       Session session = getEntityManager().unwrap(Session.class);
        OTCountryKey xform = new OTCountryKey();
        StoredProcedureQuery procedure = session.createStoredProcedureCall("MY_PKG.X_TEST", OTCountryKey.class);
        procedure.registerStoredProcedureParameter("xform", OTCountryKey.class, ParameterMode.INOUT);
        procedure.setParameter("xform", xform);
        procedure.execute();
        Object output = procedure.getOutputParameterValue("xform");

When executing the code I receive the error message:
java.lang.NumberFormatException: For input string: “DE”
when only setting the amount field and nothing else:
HibernateException: Could not convert ‘java.math.BigDecimal’ to ‘java.lang.String’ using ‘org.hibernate.type.descriptor.java.StringJavaType’ to wrap

The stored procedure is called but the attributes are not in the correct sequence.
Is there something I have forgotten to configure?

6

Yes, you must configure the columns to be in the right sequence, otherwise the serialization/deserialization might fail. Also see Hibernate ORM User Guide and the callout:

One very important thing to note is that the order of columns in the DDL definition of a type must match the order that Hibernate expects. By default, the order of columns is based on the alphabetical ordering of the embeddable type attribute names.

It continues showing that you can use @Struct(name = "OT_COUNTRYKEY", attributes = {"countryCode", "countryName", "amount"})

7

Super, I have extended the @Struct-Definition and it works but how should this work with a structure, I mean with @Embedded within an @Embedded or the @Embedded contains a list of embeddable objects. I cannot find any information about this in the Hibernate Doku. Neither for 6.x nor 7.0.
Can you point me to a documentation?
Is it feasible, in general?

8

If you read the documentation section I pointed you to carefully, you will understand that the order for every @Struct embeddable has to match the order in which you list the columns in the create type statement. Nesting works just fine.