CODEWITHSUNDEEP
javaoracle-databasehibernatestored-procedures
Dilma
Dilma

Reputation: 645

Oracle stored procedure with OUT parameters calling from Hibernate

I called a stored procedure with OUT parameter through Hibernate and got the following error:

17:30:52,646 ERROR [STDERR] Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_ACTIVITY_RECORDS'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_ACTIVITY_RECORDS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

My Stored Procedure:

  create or replace 
    PACKAGE BODY  "TEST_NESTEDTAB_PKG" AS

      PROCEDURE GET_ACTIVITY_RECORDS( TEMP_RESULT OUT ACT_LOG_TYPE, USERNAME IN ACTIVITY_LOG.USER_NAME%TYPE) AS
      BEGIN
        SELECT * BULK COLLECT INTO TEMP_RESULT
        FROM ACTIVITY_LOG
        WHERE USER_NAME  = USERNAME;
      END GET_ACTIVITY_RECORDS;

    END TEST_NESTEDTAB_PKG;

Hibernate mapping:

 <sql-query name="getactivityLog" callable="true">
    <return class="com.test.ps.domain.SPActiveLog">
        <return-property name="activityId" column="ACTIVITY_ID" />
        <return-property name="username" column="USER_NAME" />
        <return-property name="activityDate" column="ACTIVITY_DATE" />
        <return-property name="activity" column="ACTIVITY" />
        <return-property name="userId" column="USERID" />
        <return-property name="action" column="ACTION" />
        <return-property name="userRole" column="USER_ROLE" />
        <return-property name="description" column="DESCRIPTION" />
        <return-property name="category" column="CATEGORY" />
        <return-property name="result" column="RESULT" />
    </return>
    { call TEST_NESTEDTAB_PKG.GET_ACTIVITY_RECORDS(?,:USERNAME) }
</sql-query>

I called the stored procedure as follows:

    Query qry = getSession().getNamedQuery("getactivityLog");       
    qry.setParameter("USERNAME", "FACTORY TOPTAR TOTAIN");
    Object result = qry.list();

Can anyone explain what is wrong with this code?

Upvotes: 0

Views: 6024

Answers (1)

Multisync
Multisync

Reputation: 8787

Try this:

{ ? = call TEST_NESTEDTAB_PKG.GET_ACTIVITY_RECORDS(:USERNAME) }

Documentation: https://docs.jboss.org/hibernate/stable/core.old/reference/en/html/querysql-namedqueries.html

16.2.2. Using stored procedures for querying

Hibernate 3 introduces support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

CREATE OR REPLACE FUNCTION selectAllEmployments
    RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
 SELECT EMPLOYEE, EMPLOYER,
 STARTDATE, ENDDATE,
 REGIONCODE, EID, VALUE, CURRENCY
 FROM EMPLOYMENT;
      RETURN  st_cursor;
 END;

To use this query in Hibernate you need to map it via a named query.

<sql-query name="selectAllEmployees_SP" callable="true">
    <return alias="emp" class="Employment">
        <return-property name="employee" column="EMPLOYEE"/>
        <return-property name="employer" column="EMPLOYER"/>
        <return-property name="startDate" column="STARTDATE"/>
        <return-property name="endDate" column="ENDDATE"/>
        <return-property name="regionCode" column="REGIONCODE"/>
        <return-property name="id" column="EID"/>
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
    </return>
    { ? = call selectAllEmployments() }
</sql-query>

Notice stored procedures currently only return scalars and entities. and are not supported.

Upvotes: 1

Related Questions