🗄️⚙️ How to call ORACLE STORED PROCEDURES and FUNCTIONS...

Return to site

🗄️⚙️ How to call ORACLE STORED PROCEDURES and FUNCTIONS with JPA and HIBERNATE from Java champion Vlad Mihalcea

· jakartaee

🔸 TL;DR

Calling Oracle stored procedures and functions from JPA/Hibernate is not hard.

The tricky part is knowing what Oracle returns:

▪️ a simple OUT parameter

▪️ a SYS_REFCURSOR

▪️ a function return value

▪️ an entity result set needing explicit mapping

JDBC driver support and Hibernate version matter. ⚠️

🔸 WHY IT MATTERS

Stored procedures are still common in enterprise systems.

With Oracle databases, you may need to call existing PL/SQL logic from Java without rewriting everything in the service layer.

🔸 1️⃣ STORED PROCEDURE WITH OUT PARAMETER

StoredProcedureQuery query = entityManager
 .createStoredProcedureQuery("count_comments")
 .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
 .registerStoredProcedureParameter(2, Long.class, ParameterMode.OUT)
 .setParameter(1, 1L);
try {
 query.execute();
 Long commentCount =
 (Long) query.getOutputParameterValue(2);
} finally {
 query.unwrap(ProcedureOutputs.class).release();
}

This calls a procedure with one input and one output parameter. release() explicitly closes the underlying CallableStatement.

🔸 2️⃣ STORED PROCEDURE RETURNING SYS_REFCURSOR

StoredProcedureQuery query = entityManager
 .createStoredProcedureQuery("post_comments")
 .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
 .registerStoredProcedureParameter(2, Class.class, ParameterMode.REF_CURSOR)
 .setParameter(1, 1L);
query.execute();
List<Object[]> postComments = query.getResultList();

A SYS_REFCURSOR behaves like a result set. Hibernate returns rows as Object[], so column order matters.

🔸 3️⃣ ORACLE FUNCTION FROM SQL

BigDecimal commentCount = (BigDecimal) entityManager
 .createNativeQuery(
 "SELECT fn_count_comments(:postId) FROM DUAL"
 )
 .setParameter("postId", 1L)
 .getSingleResult();

For simple Oracle functions, native SQL is often the most direct approach.

🔸 WHEN RETURNING ENTITIES

For entity results, avoid relying only on Object[].

Use explicit mapping:

▪️ @SqlResultSetMapping

▪️ @EntityResult

▪️ @FieldResult

This avoids fragile column-order assumptions.

🔸 TAKEAWAYS

▪️ Use StoredProcedureQuery for OUT parameters.

▪️ Use REF_CURSOR for Oracle cursors.

▪️ Use native SQL for simple function calls.

▪️ Use JDBC for edge cases.

▪️ Use explicit mappings when returning entities.

JPA abstracts a lot.

But with stored procedures and functions, the database still matters. 🧠

#Java #Hibernate #JPA #OracleDatabase #SpringBoot #BackendDevelopment #SQL #Database #EnterpriseJava #SoftwareEngineering

Original article: https://vladmihalcea.com/how-to-call-oracle-stored-procedures-and-functions-from-hibernate/

Go further with Java certification:

Java👇

https://bit.ly/javaOCP

Spring👇

https://bit.ly/2v7222

SpringBook👇

https://bit.ly/springtify

JavaBook👇

https://bit.ly/jroadmap