How to call Oracle stored procedures and functions with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.

Oracle stored procedures

Oracle supports both stored procedures and functions, so first we’ll start with the following stored procedure that outputs a simple value.

Oracle stored procedure outputting a simple value

CREATE OR REPLACE PROCEDURE count_comments (  
   postId IN NUMBER,  
   commentCount OUT NUMBER )  
AS  
BEGIN  
    SELECT COUNT(*) INTO commentCount  
    FROM post_comment  
    WHERE post_id = postId; 
END;

This stored procedure has two parameters: an input parameter (e.g. postId) and an output parameter (e.g. commentCount) which is used to return the count of post_comment records associated with a given post_id parent row.

To call this stored procedure, you can use the following Java Persistence API 2.1 syntax:

StoredProcedureQuery query = entityManager
.createStoredProcedureQuery("count_comments")
.registerStoredProcedureParameter(
    1, 
    Long.class, 
    ParameterMode.IN
)
.registerStoredProcedureParameter(
    2, 
    Long.class, 
    ParameterMode.OUT
)
.setParameter(1, 1L);

query.execute();

Long commentCount = (Long) query.getOutputParameterValue(2);

By default, the underlying JDBC CallableStatement remains open even after executing the stored procedure and fetching the OUT or REF_CURSOR parameters.

To explicitly close the CallableStatement, you have to call release on the underlying ProcedureOutputs object as illustrated in the following example:

  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);
  
      assertEquals(Long.valueOf(2), commentCount);
  } finally {
      query.unwrap(ProcedureOutputs.class)
      .release();
  }
  

For more details, check out this article.

Oracle stored procedure outputting a SYS_REFCURSOR

A stored procedure can also define a SYS_REFCURSOR output parameter which is associated with a database cursor that can be iterated to fetch multiple database records:

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS  
BEGIN 
    OPEN postComments FOR 
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

On Oracle, it’s possible to call this stored procedure using the JPA 2.1 syntax:

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();

Unlike the previous stored procedure call, this time, we are using getResultList() and we obtain an Object[] containing all column values associated with the selected database records.

Hibernate has long been offering its own stored procedure API, and calling the aforementioned stored procedure can be done as follows:

Session session = entityManager.unwrap(Session.class);
ProcedureCall call = session
.createStoredProcedureCall("post_comments");
    
call.registerParameter(
    1, Long.class, 
    ParameterMode.IN
)
.bindValue(1L);
    
call.registerParameter(
    2, 
    Class.class, 
    ParameterMode.REF_CURSOR
);

Output output = call.getOutputs().getCurrent();
if (output.isResultSet()) {
    List<Object[]> postComments = ((ResultSetOutput) output)
    .getResultList();
}

Oracle functions

Oracle also supports database functions, which, unlike stored procedures, don’t use input and output parameters, but one or more function arguments and a single return value.

Oracle function returning a simple value

The first stored procedure can be turned into a function which looks like this:

CREATE OR REPLACE FUNCTION fn_count_comments ( 
    postId IN NUMBER ) 
    RETURN NUMBER 
IS 
    commentCount NUMBER; 
BEGIN 
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment 
    WHERE post_id = postId; 
    RETURN( commentCount ); 
END;

Unfortunately, as of writing (Hibernate 5.1.0), both the Java Persistence 2.1 stored procedure and the Hibernate-specific API cannot be used to call functions.
However, there are several workarounds for this limitation.

First, we can simply call the Oracle function just like any other SQL query:

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

Another approach is to call the database function using plain JDBC API:

Session session = entityManager.unwrap( Session.class );

Integer commentCount = session.doReturningWork( 
    connection -> {
    try (CallableStatement function = connection
        .prepareCall(
            "{ ? = call fn_count_comments(?) }" )) {
        function.registerOutParameter( 1, Types.INTEGER );
        function.setInt( 2, 1 );
        function.execute();
        return function.getInt( 1 );
    }
} );

Oracle function returning a SYS_REFCURSOR

Just like with stored procedures, the Oracle function can also return a SYS_REFCURSOR, and, to make the example even more interesting, we are going to fetch a Post along with its associated PostComment child entities.

The Oracle function looks as follows:

CREATE OR REPLACE FUNCTION fn_post_and_comments ( 
    postId IN NUMBER ) 
    RETURN SYS_REFCURSOR 
IS 
    postAndComments SYS_REFCURSOR; 
BEGIN 
   OPEN postAndComments FOR 
        SELECT 
            p.id AS "p.id", 
            p.title AS "p.title", 
            p.version AS "p.version", 
            c.id AS "c.id", 
            c.post_id AS "c.post_id", 
            c.version AS "c.version", 
            c.review AS "c.review" 
       FROM post p 
       JOIN post_comment c ON p.id = c.post_id 
       WHERE p.id = postId; 
   RETURN postAndComments; 
END;

To fetch entities, we need to instruct Hibernate of the mapping between the underlying ResultSet and each entity property. This can be done using the Hibernate-specific NamedNativeQuery annotation because, unlike the JPA NamedNativeQuery, it also supports calling stored procedures and database functions.

The NamedNativeQuery mapping looks like this:

@NamedNativeQuery(
    name = "fn_post_and_comments",
    query = "{ ? = call fn_post_and_comments( ? ) }",
    callable = true,
    resultSetMapping = "post_and_comments"
)
@SqlResultSetMapping(
    name = "post_and_comments",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult( 
                    name = "id", 
                    column = "p.id" 
                ),
                @FieldResult( 
                    name = "title", 
                    column = "p.title" 
                ),
                @FieldResult( 
                    name = "version", 
                    column = "p.version" 
                ),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult( 
                    name = "id", 
                    column = "c.id" 
                ),
                @FieldResult( 
                    name = "post", 
                    column = "c.post_id" 
                ),
                @FieldResult( 
                    name = "version", 
                    column = "c.version" 
                ),
                @FieldResult( 
                    name = "review", 
                    column = "c.review" 
                ),
            }
        )
    }
)

As you can see, you need to provide a SqlResultSetMapping if you wish to return entities instead of an Object[] array.

With these two mappings in place, fetching the Post and the PostComment entities looks like this:

List<Object[]> postAndComments = entityManager
.createNamedQuery("fn_post_and_comments")
.setParameter(1, 1L)
.getResultList();
    
Object[] postAndComment = postAndComments.get(0);
Post post = (Post) postAndComment[0];
PostComment comment = (PostComment) postAndComment[1];

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Seize the deal! 50% discount. Seize the deal! 50% discount.

Conclusion

Calling stored procedures and functions is not difficult at all, but it requires knowing some details about Hibernate and the underlying JDBC driver capabilities. The next article is about calling SQL Server stored procedures and functions, so stay tuned!

FREE EBOOK

25 Comments on “How to call Oracle stored procedures and functions with JPA and Hibernate

  1. Hi Vlad,

    When I try to use the code for calling a stored procedure that returns a simple value, VARCHAR2, i get the following error:

    org.hibernate.HibernateException: Could not unwrap this [org.hibernate.procedure.internal.ProcedureCallImpl@482215b0] as requested Java type [org.hibernate.procedure.ProcedureOutputs]

    The stored procedure works fine when used with a straight JDBC “call” and is a fairly old procedure. I’m wondering if there’s an issue with the stored procedure itself. I should note that I’m not a database expert by any stretch of the imagination.

  2. Is it possible to return a double cursor from a stored procedure?

    • Using JDBC, it could be possible. If that does not work with Hibernate, then you need to open a Jira issue for this.

  3. Hi Vlad, I am trying to call a function which return a SYS_REFCURSOR, but has 3 out number parameters (I know, it’s strange).

    How can I call that function? At the moment I am using SimpleJdbcCall and it works fine, but it seams to be slow.

    Any idea?

    • SimpleJdbcCall just delegates the call to the JDBC CallableStatement, so if the call is slow, it could be because the stored procedure execution is slow. Try running it from the command line and see how fast it is.

  4. Hi Vlad, thanks for your amazing website, I’ve been surfing it for a while now, concepts are very well exposed and you give a lot of insight regarding proposed solution’s performace.

    My question now is which solution to choose from:
    – creating an entity from a select statement (whether it’s a simple select, a subselect or a view) or
    – returning a list of OUT parameters (perhaps a cursor?) from a stored procedure and calling it from Hibernate or JDBC.

    What are the considerations I should have before making the choice?

    Thanks again for sharing your knowledge!

  5. Hi Vlad, I also have the same issue with the Oracle9i dialect stack trace – I watched your video and looked at the class but it didn’t resolve the issue. Do you have any other possible suggestions? I am using Spring Boot, Java 8, hibernate versions;
    Hibernate Core {5.3.9.Final}
    Hibernate Commons Annotations {5.0.4.Final}
    Thanks

    Caused by: java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle9iDialect does not support resultsets via stored procedures

  6. How to call oracle stored procedure in spring data jpa with input parameter as arraylist? I tried using createArrayOf(“NUMBER”, array) but it is throwing unsupported feature exception.. Could you please tell me how can i solve this?

    • You need to inject the EntityManager in your Repository and pass the parameter as-is. If it does not work, it’s a JPA provider bug, so you’ll need to open a Jira issue.

  7. Thank you very much!
    We tried many ways … with parameter-names, but that doesn’t work at all.
    We chose your solution with working with paramter-numbers, that solved the issue!
    THANKS AGAIN!!!

  8. Hi Vladi.

    Can you tell me what is session package in:

    Session session = entityManager.unwrap( Session.class );

    Thank a lot!

    • Are you asking me what’s the package of the Hibernate Session interface?

  9. Does someone sees a danger on doing
    ((ProcedureCallImpl) query).getOutputs().release(); ?

    Does the statement gets shared between calls to the database or cached ?

    Does closing the statement represents any danger ?

    Thanks

    • These are very good questions. I’ll check it out and write an article when I have some time.

  10. Ok, the solution i just found is using Dmitrii comment.

    StoredProcedureQuery query = session.createStoredProcedureQuery(procedimiento);

    if (query instanceof ProcedureCallImpl) {
    ((ProcedureCallImpl) query).getOutputs().release();
    }

    And using that the cursor gets closed

  11. I’m calling a soted procedure to create a document. It contains complex logic and it needs to be called for each document to be stored.

    It has an XML as input parameter and a LONG value as output.

    What could be the reason for a cursor to remain open in a situation like that ? (Hibernate 5.3.7)

    • Not iterating the ResultSet, maybe? You need to debug it and see why a cursor would not be closed. It could be a bug too. Try to replicate it, and if you can, you need to open Jira issue.

  12. I have a test case, calling 300 times a stored procedure, and in the call numbebr 292 it gives me ORA-01000. Every call is inside the same transaction and the procedure call is in a dedicated method.
    Shouldn’t hibernate close the used cursors ? or i’m doing something wrong ?

    • As long as you read the ResultSet, I suppose the cursor should get closed. But calling a SP 300 times in a transaction sounds like a code smell. Why don’t you fetch what you need with fewer calls. Anyway, without seeing the code and the log messages, it’s unlikely to understand what’s wrong.

  13. Hi Vilad,
    Thanks for the awesome post. Can u help to call a SP which accepts an array as IN parameter. I tried several ways but somehow types are not matching. The sql code to create SP and respective JPA 2.1 code given below.

    create or replace PACKAGE BODY PKG_TEST AS
    TYPE Str_Array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    PROCEDURE TEST( V_ARGS IN Str_Array) AS

    BEGIN
    …….
    END TEST;

    StoredProcedureQuery query = em.createStoredProcedureQuery(“PKG_TEST.TEST”);
    query.registerStoredProcedureParameter(1, String[].class, ParameterMode.IN);
    query.execute();

    I am getting error :

    java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to ‘TEST’
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    • When working with Oracle custom Types, you need to use the Oracle-specific oracle.sql.STRUCT:

      STRUCT strArray = new STRUCT (...);
      ps.setObject(1, strArray, Types.STRUCT);
      

      Try it with JDBC first and then see if you can pass the STRUCT to the JPA or Hibernate StoredProcedureQuery.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.