Calling Oracle Stored Procedure from Hibernate

While calling Oracle stored procedure from Hibernate  there is couple of rules that we need to follow. There can be only one return value and this must be a reference cursor. This return reference cusor must be the first and only out value for the stored procedure. It is required that stored procedure must return a reference cursor to be used from Hibernate.

Here is my stored procedure which return book name and ISBN number for a given branch and for a given author

CREATE OR REPLACE PROCEDURE SP_LIB_DTL(p_cursor    out sys_refcursor,
                                       in_brnch_cd in number,
                                       in_auth_cd in number)
as
  bookName varchar2(8);
  ISBN     number;
begin
  bookName := null;
  ISBN     := 0;
  open p_cursor for
    select l.book_name, l.isbn_nbr
      into bookName, ISBN
      from LIB_BRNCH_DTL l
     where l.branch_code = in_brnch_cd
     and l.auth_code = in_auth_cd;

end;

HIbernate Mapping for return class and Named Query:

<?xml version=”1.0″ encoding=”utf-8″?>
<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd“>
<hibernate-mapping>
<class name=”com.org.lib.LibraryDetails”>
<id name=”ISBN” type=”long” />
<property name=”bookName” type=”string” />
</class>
 <sql-query name=”LIB_SP” callable=”true”>
 <return class=”com.org.lib.LibraryDetails”>
   <return-property name=”ISBN” column=”isbn_nbr” />
   <return-property name=”bookName” column=”book_name” />
 </return>
  {  call SP_LIB_DTL(? , :branchCD ,:authorCD) }
 </sql-query>
</hibernate-mapping>

Make sure you have used the correct database field name value for the column attribute for the return property mapping. You will get the following error if you don’t map the correct databse field name

could not execute query; bad SQL grammar [{  call SP_LIB_DTL(? , ?) }];
nested exception is java.sql.SQLException: Invalid column name

Here is the DAO implementation for executing query and to set the bind parameter values.

public  List selectBooks(final BigDecimal branchCode,final BigDecimal authorCode){
        return (List) getHibernateTemplate().execute(new HibernateCallback() {
              public Object doInHibernate(Session session) throws HibernateException, SQLException
              {
                  Query q = session.getNamedQuery(“LIB_SP”);
                  q.setLong(“branchCD”, branchCode.longValue());
                  q.setLong(“authorCD”, authorCode.longValue());
                  return q.list();
              }
          });
      }

19 Responses to “Calling Oracle Stored Procedure from Hibernate”

  • Gobi:

    Thank s for this post. I have another doubt. If i want to return a string frm the procedure how should i configure that in the hbm file?
    also how should i call in the java?

  • Dhanya:

    You need to open a cursor and return the cursor. Curosr can have String return out parameter in it. In the given example cursor has ‘bookName’ out parameter which is mapped to a property in the LibraryDetails class in the hibernate mapping. When you call the stored procedure you get a list of LibraryDetails object. Just need to iterate through the list and get LibraryDetails object values out of it. Hope this helps..

  • Thiago Cavalieri da Silveira:

    I did call a stored procedure returning a cursor, but i can’t call a stored function returning a number for exemple.

    How can i do that?

    My hibernate maping:

    { ? = call PA_TIPO_LOCAL.INC_TIPO_LOCAL() }

    My java code:

    Query q = ManageDAO.getSession().getNamedQuery(“incTipoLocal”);

    q.uniqueResult();

    The Error:

    Hibernate:
    { ? = call PA_TIPO_LOCAL.INC_TIPO_LOCAL() }
    16:27:31,546 WARN JDBCExceptionReporter:100 – SQL Error: 6550, SQLState: 65000
    16:27:31,562 ERROR JDBCExceptionReporter:101 – ORA-06550: line 1, column 13:
    PLS-00382: expression is of wrong type
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Exception in thread “main” org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2231)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    at org.hibernate.loader.Loader.list(Loader.java:2120)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1722)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:835)
    at br.es.csi.cad.dao.TipoLocalDAO.incTipoLocal(TipoLocalDAO.java:30)
    at br.es.csi.cad.dao.TipoLocalDAO.main(TipoLocalDAO.java:38)
    Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
    PLS-00382: expression is of wrong type
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Can you help me, please?

    Thanks.

  • Brian Bohl:

    Per the Hibernate Reference doc (http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#sp_query)

    For Oracle the following rules apply:

    *A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

  • Pino:

    And what about a stored procedure that returns nothing? My stored proc has only input parameters. How can i confiugure and call it?

  • Antony:

    calling storeprocedure which returns nothing.

    { call P_TEST.SP_UPDATE_TEST_USER(:id,:displayName) }

    ———————————————————————————-
    procedure sp_update_test_user(intId in integer, vchDisplayName in varchar2)is
    begin
    update test_user tu set tu.display_name=vchDisplayName where tu.id=intId;
    end sp_update_test_user;
    ——————————————————————————–
    public boolean updateUserDB(){
    Transaction tx = null;
    Session session = HibernateUtil.getInstance().getCurrentSession();
    try {
    System.out.println(“********** UpdateUserDB *************”);
    System.out.println(“id –> “+paramBean.getId());
    System.out.println(“DisplayName –> “+paramBean.getDisplayName());
    System.out.println(“*************************************”);
    tx = session.beginTransaction();
    Query query = session.getNamedQuery(“SP_UPDATE_TEST_USER”);
    query.setInteger(“id”, Integer.parseInt(“”+paramBean.getId()));
    query.setString(“displayName”, paramBean.getDisplayName());
    int retInt=query.executeUpdate();
    tx.commit();
    return true;
    } catch (RuntimeException e) {
    if (tx != null && tx.isActive()) {
    try {
    // Second try catch as the rollback could fail as well
    tx.rollback();
    } catch (HibernateException e1) {
    logger.debug(“Error rolling back transaction”);
    }
    // throw again the first exception
    throw e;
    }
    return false;
    }
    }
    ———————————————————————————–

    I hope this will help you.

  • Pino,
    Try to define a REF CURSOR and return the number of rows affected by the insert/update/delete operation
    Hope this helps..

  • Ridwaan:

    Hi,

    Thanks for the nice post.

    I am trying to call a stored function from Hibernate.

    The function:

    create or replace
    FUNCTION FCT_CRYPTE_PWD(input_string IN VARCHAR2) RETURN VARCHAR2
    IS
    hex_digest varchar2(32);
    digest varchar2(16);
    BEGIN
    digest := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => input_string);
    SELECT Lower(RAWTOHEX(digest)) INTO hex_digest FROM dual;
    RETURN hex_digest;
    END;

    Is it possible to do so?
    If Yes, How should the mapping and acces logic be?

  • subhasish sahu:

    im getting this error while executing store procedure through hibernate :

    org.hibernate.HibernateException: Errors in named queries: CHECKUSERID1

    { ? = call CHECKUSERID1(?) }

    —————————————————————–

    package component;

    import org.hibernate.*;
    import org.hibernate.cfg.*;
    import java.util.List;

    public class ProcedureCall
    {
    public static void main(String args[])
    {
    Session session = null;
    SessionFactory factory = null;
    Transaction t = null;

    try
    {
    factory = new Configuration().configure().buildSessionFactory();
    session = factory.openSession();
    Query query = session.getNamedQuery(“CHECKUSERID1”);
    List results = query.list();
    session.close();
    }
    catch(Exception e)
    {
    System.out.println(e.toString());
    }
    }
    }

    ———————————————————–

    CREATE OR REPLACE PROCEDURE CHECKUSERID1
    (

    ref_cursor OUT SYS_REFCURSOR
    )
    AS
    IC_USER_TYPE varchar2(8);
    BEGIN

    OPEN ref_cursor FOR

    SELECT IC_USER_TYPE into IC_USER_TYPE from USER_PROFILE
    where trim(IC_USER_ID)=’SS1000′;

    END CHECKUSERID1;
    /

  • subhasish sahu:

    sorry didnt mention the hbm file

    SQE_TABLE_CONTACT

    { ? = call CHECKUSERID1(?) }

  • subhasish sahu:

    { ? = call CHECKUSERID1(?) }

  • Lila:

    And how in that case to act?

  • Puru:

    I am new to hibernate and java.
    I am doing a sample application to call the SP from Hibernate. I get the List but how do I see the values contained in the list. when I iterare the List ..it gives me the object but not the actual value.

    Mapping:

    { call SP_LIB_DTL(? , :branchCD ,:authorCD) }

    ============
    Code to invoke the SP
    Query q = session.getNamedQuery(“LIB_SP”);
    q.setLong(“branchCD”, lib.getBranch_Code());
    q.setLong(“authorCD”, lib.getAuth_Code());
    //@SuppressWarnings(“unchecked”)
    System.out.println(“before getting the list”);
    List list= q.list();
    System.out.println(“size of the list is ” + list.size());
    for(int i=0; i<list.size();i++){
    System.out.println("list get"+list.get(i));
    }
    System.out.println("after list");
    ==========================

    results:
    Hibernate: { call SP_LIB_DTL(? , ? ,?) }
    size of the list is 1
    list getcom.fidelity.LibraryDetails@f0c85e
    after list

    Appreciate your help

    Puru

  • sandeep katakol:

    My SP returns varaible number of columns depending on the paremeters passed…
    How should I map the columns in .xml file

  • Madan Mohan:

    hi, this is my oracle stored procedure
    CREATE OR REPLACE PROCEDURE sp_pos_pur_resp (
    p_cursor OUT sys_refcursor,
    hashed_pan IN VARCHAR2,
    acc_type IN VARCHAR2,
    trans_amt IN NUMBER
    )
    AS
    bal NUMBER;
    response VARCHAR2 (100);
    BEGIN
    reponse := NULL;
    bal := 0;

    OPEN p_cursor FOR
    SELECT a.current_balance
    INTO bal
    FROM prod_accounts a, prod_card c
    WHERE c.prod_card_id = a.prod_card_id
    AND c.prod_card_hased_pan = hashed_pan
    AND a.primaryaccttype = acc_type;
    bal := bal – trans_amt;
    IF bal < 0
    THEN
    response := 'in sufficient fund';
    END IF;
    END;

    here this procedure returns balance & response.
    response is not a column in table.
    please tell me how can
    i configure that in the hbm file?
    also how should i call in the java?

    Thanks in advance.

  • Anonymous:

    This is in response to a question i got in mail.

    You can use Oracle default table Dual , if the return is not from a table field . For example

    CREATE OR REPLACE PROCEDURE Test_SP
    (p_cursor out sys_refcursor,in_id in number)
    as
    response VARCHAR2 (100);

    begin
    if in_id < 0
    THEN
    response := 'Insufficient Fund';
    ELSE
    response := 'YES';
    END IF;

    open p_cursor for
    select response from Dual;

    end;

  • You can use the Oracle default table Dual if the return value is not a database table field

    CREATE OR REPLACE PROCEDURE Test_SP
    (p_cursor out sys_refcursor,in_id in number)
    as
    response VARCHAR2 (100);

    begin
    if in_id < 0
    THEN
    response := 'Insufficient Fund';
    ELSE
    response := 'YES';
    END IF;

    open p_cursor for
    select response from Dual;

    end;

  • Here is an example for hibdernate mapping for the stored procedure Test_SP in the above comment. Please try this and see if this works.

    public class FundTransfer{
    private String fundTransferReply;

    }

  • You can use the Oracle default table Dual if the return value is not a database table field

    CREATE OR REPLACE PROCEDURE Test_SP
    (p_cursor out sys_refcursor,in_id in number)
    as
    response VARCHAR2 (100);

    begin
    if in_id < 0
    THEN
    response := 'Insufficient Fund';
    ELSE
    response := 'YES';
    END IF;

    open p_cursor for
    select response from Dual;

    end;

    Here is an example for the hibernate mapping for the above stored procedure Test_SP.

    public class FundTransfer{
    private String fundTransferReply;

    }

Leave a Reply

*