Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hello Hibersmartfolk,

I am having a major issue trying to get an oracle function to fire. The function is not getting executed, but I don't get any errors either...any ideas based on the following?

Appreciation in advance!

Graham


Hibernate version:
3.1.3

Mapping documents:

<?xml version="1.0"?>
<!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.encana.admin.courierlog.model.InLogOutLogReportData">
<id
name="id"
type="java.lang.Long"
column="ID">
<generator class="native" />
</id>

<property
name="logType"
type="java.lang.String" column="LOG_TYPE">
</property>

<property
name="courierDesk"
type="java.lang.String" column="COURIER_DESK">
</property>

<property
name="courier"
type="java.lang.String" column="COURIER">
</property>

<property
name="weekEndDate"
type="java.lang.String" column="WEEK_END_DATE">
</property>

<property
name="weeklyTotal"
type="java.lang.Long" column="WEEKLY_TOTAL">
</property>

<property
name="monthlyTotal"
type="java.lang.Long" column="MONTHLY_TOTAL">
</property>

</class>

<sql-query name="returnInLogOutLogRptData_SP" callable="true">
<return alias="rptInLogOutLogReportData" class="com.encana.admin.courierlog.model.InLogOutLogReportData">
<return-property name="id" column="ID" />
<return-property name="logType" column="LOG_TYPE"/>
<return-property name="courierDesk" column="COURIER_DESK"/>
<return-property name="courier" column="COURIER"/>
<return-property name="weekEndDate" column="WEEK_END_DATE"/>
<return-property name="weeklyTotal" column="WEEKLY_TOTAL"/>
<return-property name="monthlyTotal" column="MONTHLY_TOTAL"/>
</return>
{ ? = call COURIER_LOG.FN_ILOG_OLOG_DATA_RPT(:pUserId, :pYear) }
</sql-query>

</hibernate-mapping>


Java Code:

public class InLogOutLogReportDataDAOImpl extends HibernateDaoSupport
implements InLogOutLogReportDataDAO {

private static final int LIST_INITIAL_CAPACITY = 50;

public List getInLogOutLogReportData (final Long year) {

final Session session = new Configuration().configure().buildSessionFactory().openSession();

List inLogOutLogReportData = new ArrayList(LIST_INITIAL_CAPACITY);

try {
inLogOutLogReportData = session.getNamedQuery("returnInLogOutLogRptData_SP")
.setParameter("pUserId", "GHEATH")
.setInteger("pYear", year.intValue())
.list();

} catch (HibernateException ex) {

} finally {
try {
if (session != null) {
session.close();
}
} catch (HibernateException ex) {

}
}

return inLogOutLogReportData;

}

}

Name and version of the database you are using: Oracle 9i: This is my oracle function:

CREATE OR REPLACE FUNCTION FN_ILOG_OLOG_DATA_RPT (PUSER_ID IN COURIER_LOG.INCOMING_LOGS.CONSIGNEE_EMPLID%TYPE, PYEAR INT)
RETURN COURIER_LOG.TYPES.ref_cursor
AS

currentDate DATE := TO_DATE('01/01/' || TO_CHAR(PYEAR), 'MM/DD/YYYY');
yearEndDate DATE := TO_DATE('12/31/' || TO_CHAR(PYEAR), 'MM/DD/YYYY');

ILOG_OLOG_REPORT_CURSOR COURIER_LOG.TYPES.ref_cursor;

BEGIN

DELETE FROM COURIER_LOG.TEMP_REPORTING_ILOG_OLOG_DATA
WHERE (UPPER(USER_ID) = UPPER(PUSER_ID));

DELETE FROM COURIER_LOG.TEMP_REPORTING_WEEK_END_DATES
WHERE (UPPER(USER_ID) = UPPER(PUSER_ID));

WHILE currentDate <= yearEndDate
LOOP
IF (TO_CHAR(currentDate, 'D') = '7') THEN
INSERT INTO COURIER_LOG.TEMP_REPORTING_WEEK_END_DATES
SELECT PUSER_ID,
TO_CHAR(currentDate, 'YYYY'),
TO_CHAR(currentDate, 'MM'),
TO_CHAR(currentDate, 'DD')
FROM DUAL;
END IF;

currentDate := currentDate + 1;

END LOOP;


INSERT INTO COURIER_LOG.TEMP_REPORTING_ILOG_OLOG_DATA
SELECT UPPER(PUSER_ID),
IL.COURIER_DESK_ID,
IL.COURIER_ID,
'Incoming Logs',
TRWED.YEAR,
TRWED.MONTH,
TRWED.WEEK_END_DAY,
SUM(IL.NUMBER_OF_PIECES),
0
FROM COURIER_LOG.INCOMING_LOGS IL,
COURIER_LOG.TEMP_REPORTING_WEEK_END_DATES TRWED
WHERE (TO_CHAR(IL.RECEIVED_AT, 'YYYY') = PYEAR)
AND (UPPER(TRWED.USER_ID) = UPPER(PUSER_ID))
AND (IL.RECEIVED_AT >= TO_DATE(TRWED.MONTH || '/' || TRWED.WEEK_END_DAY || '/' || TRWED.YEAR, 'MM/DD/YYYY') - 6)
AND (IL.RECEIVED_AT <= TO_DATE(TRWED.MONTH || '/' || TRWED.WEEK_END_DAY || '/' || TRWED.YEAR, 'MM/DD/YYYY'))
GROUP BY
IL.COURIER_DESK_ID,
IL.COURIER_ID,
TRWED.YEAR,
TRWED.MONTH,
TRWED.WEEK_END_DAY;


INSERT INTO COURIER_LOG.TEMP_REPORTING_ILOG_OLOG_DATA
SELECT UPPER(PUSER_ID),
OL.COURIER_DESK_ID,
OL.COURIER_ID,
'Outgoing Logs',
TRWED.YEAR,
TRWED.MONTH,
TRWED.WEEK_END_DAY,
SUM(OL.NUMBER_OF_PIECES),
0
FROM COURIER_LOG.OUTGOING_LOGS OL,
COURIER_LOG.TEMP_REPORTING_WEEK_END_DATES TRWED
WHERE (TO_CHAR(OL.SENT_AT, 'YYYY') = PYEAR)
AND (UPPER(TRWED.USER_ID) = UPPER(PUSER_ID))
AND (OL.SENT_AT >= TO_DATE(TRWED.MONTH || '/' || TRWED.WEEK_END_DAY || '/' || TRWED.YEAR, 'MM/DD/YYYY') - 6)
AND (OL.SENT_AT <= TO_DATE(TRWED.MONTH || '/' || TRWED.WEEK_END_DAY || '/' || TRWED.YEAR, 'MM/DD/YYYY'))
GROUP BY
OL.COURIER_DESK_ID,
OL.COURIER_ID,
TRWED.YEAR,
TRWED.MONTH,
TRWED.WEEK_END_DAY;

UPDATE COURIER_LOG.TEMP_REPORTING_ILOG_OLOG_DATA TRIOD
SET MONTHLY_TOTAL = (SELECT SUM(TRIOD2.WEEKLY_TOTAL)
FROM COURIER_LOG.TEMP_REPORTING_ILOG_OLOG_DATA TRIOD2
WHERE (UPPER(TRIOD2.USER_ID) = UPPER(PUSER_ID))
AND (UPPER(TRIOD2.USER_ID) = UPPER(TRIOD.USER_ID))
AND (TRIOD2.LOG_TYPE = TRIOD.LOG_TYPE)
AND (TRIOD2.COURIER_DESK_ID = TRIOD.COURIER_DESK_ID)
AND (TRIOD2.COURIER_ID = TRIOD.COURIER_ID)
AND (TRIOD2.YEAR = TRIOD.YEAR)
AND (TRIOD2.MONTH = TRIOD.MONTH)
GROUP BY
TRIOD2.USER_ID,
TRIOD2.LOG_TYPE,
TRIOD2.COURIER_DESK_ID,
TRIOD2.COURIER_ID,
TRIOD2.YEAR,
TRIOD2.MONTH
HAVING TRIOD.WEEK_END_DATE = MAX(TRIOD2.WEEK_END_DATE));


OPEN ILOG_OLOG_REPORT_CURSOR FOR
SELECT ROWNUM AS ID,
TRIOD.LOG_TYPE,
CD.COURIER_DESK,
C.COURIER,
TO_CHAR(TO_DATE(TRIOD.MONTH || '/' || TRIOD.WEEK_END_DATE || '/' || TRIOD.YEAR, 'MM/DD/YYYY'), 'MON-DD') AS WEEK_END_DATE,
WEEKLY_TOTAL,
MONTHLY_TOTAL
FROM COURIER_LOG.TEMP_REPORTING_ILOG_OLOG_DATA TRIOD,
COURIER_LOG.COURIER_DESKS CD,
COURIER_LOG.COURIERS C
WHERE (UPPER(TRIOD.USER_ID) = UPPER(PUSER_ID))
AND (TRIOD.COURIER_DESK_ID = CD.ID)
AND (TRIOD.COURIER_ID = C.ID)
ORDER BY
TRIOD.LOG_TYPE,
CD.COURIER_DESK,
C.COURIER,
TRIOD.MONTH,
TRIOD.WEEK_END_DATE;

RETURN ILOG_OLOG_REPORT_CURSOR;

CLOSE ILOG_OLOG_REPORT_CURSOR;

END FN_ILOG_OLOG_DATA_RPT;
/

Permission issue. Apologies.