Error while executing native query with EJB3

[puneet.bansal]

Environment - JBoss 4.0.5 with EJB3 and Oracle 9.2.0.4

Hi,

I am trying to run an Oracle native query using JPA. The query runs
perfectly on SQL*Plus but I get the following error

15:51:59,209 WARN [JDBCExceptionReporter] SQL Error: 900, SQLState:
42000
15:51:59,209 ERROR [JDBCExceptionReporter] ORA-00900: invalid SQL
statement

when I try to run to run the query using createNativeQuery() and
getResultList(). It appears that the query passed to Oracle gets
changed somehow. Does anybody have any idea what is going on? I have
specified the query in xml mapping file. Here's the query block -

<named-native-query name="Trip.NativeFindTripsForCW" result-
set-mapping="tripResultSet">
<query>
SELECT
ID_TRIP,
DT_TRIP,
TRIP_DT_LAST_UPDATE,
TRIP_ID_CASE_WORKER,
TRIP_NBR_MILES_DRVN,
TXT_DUTIES_PRFRMD,
ID_WAYPOINT,
ADDR_STREET_LN,
ADDR_CITY,
ADDR_STATE,
ADDR_ZIP,
WPT_DT_LAST_UPDATE,
WPT_ID_CASE_WORKER,
DT_WPT_TMSTMP,
NBR_LATITUDE,
NBR_LONGITUDE,
NBR_SPEED,
WPT_NBR_MILES_DRVN
FROM (
SELECT
t.id_trip,
t.dt_trip,
t.DT_LAST_UPDATE AS trip_dt_last_update,
t.ID_CASE_WORKER AS trip_id_case_worker,
t.NBR_MILES_DRVN AS trip_nbr_miles_drvn,
t.TXT_DUTIES_PRFRMD,
ID_WAYPOINT,
w.DT_LAST_UPDATE AS wpt_dt_last_update,
w.ID_CASE_WORKER AS wpt_id_case_worker,
DT_WPT_TMSTMP,
ADDR_STREET_LN,
ADDR_CITY,
ADDR_STATE,
ADDR_ZIP,
NBR_LATITUDE,
NBR_LONGITUDE,
NBR_SPEED,
w.NBR_MILES_DRVN AS wpt_nbr_miles_drvn,
rank() over (PARTITION BY w.id_trip ORDER BY dt_wpt_tmstmp) AS
wpt_Rank_1,
rank() over (PARTITION BY w.id_trip ORDER BY dt_wpt_tmstmp DESC)
AS wpt_Rank_2
FROM
waypoint w, trip t
WHERE w.id_case_worker = 1
AND w.id_trip = t.id_trip
)
WHERE
wpt_rank_1 = 1
OR wpt_rank_2 = 1
ORDER BY id_trip;
</query>
</named-native-query>

Thanks for the help.
Puneet

 

[smcneill]

Have you had any success figuring this out? I'm having the same problem.


I'm using Toplink 2.0-58 as the JPA provider
and Oracle's ojdbc14 (10.1.0.4) jdbc driver.