Intermittent Error: Parameter does not exist as a named parameter

Description

We are seeing intermittent Hibernate errors in our production environment (Web Logic Instances):

Error 1: org.hibernate.QueryException: Named parameter does not appear in Query

Error 2: java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter []

The problem goes away after restarting the Web Logic instance.

  • We use Native SQL with Named Parameters

  • Database is Oracle 12c

  • Application Server: Web Logic 12c

Example SQL Error:

org.hibernate.QueryException: Named parameter does not appear in Query: staffs141_ [select distinct cs.CAPPLAN_SET_ID, cs.CAPPLAN_SET_NAME, ce.capplan_edition_id, fs.forecast_site_id,fs.site_name, sg.STAFF_GROUP_ID, sg. STAFF_GROUP_NAME , st.staff_type_id, st.staff_type_name,ce.IS_WORKING_EDITION from CAPPLAN_SETS cs , forecast_sites fs, staff_types st, staff_groups sg, forecast_groups fg, capplan_editions ce, capplans cp, edition_status_master esm where fs.FORECAST_SITE_ID = sg.FORECAST_SITE_ID and sg.staff_type_id = st.staff_type_id and sg.STAFF_GROUP_ID = fg.STAFF_GROUP_ID and fg.CAPPLAN_SET_ID = cs.CAPPLAN_SET_ID and cs.CAPPLAN_SET_ID = cp.CAPPLAN_SET_ID and cp.CAPPLAN_ID = ce.CAPPLAN_ID and cs.IS_ACTIVE ='Y' and ce.IS_WORKING_EDITION = 'Y' and ce.EDITION_STATUS_ID = esm.EDITION_STATUS_ID and esm.EDITION_STATUS_NAME not in ('What-if', 'Contingency','New') AND ce.mapping_effective_date BETWEEN NVL(fs.effective_start_date,ce.mapping_effective_date) AND NVL(fs.effective_end_date,ce.mapping_effective_date) AND ce.mapping_effective_date BETWEEN NVL(sg.effective_start_date,ce.mapping_effective_date) AND NVL(sg.effective_end_date,ce.mapping_effective_date) AND ce.mapping_effective_date BETWEEN NVL(fg.effective_start_date,ce.mapping_effective_date) AND NVL(fg.effective_end_date,ce.mapping_effective_date) AND ce.mapping_effective_date BETWEEN NVL(st.effective_start_date,ce.mapping_effective_date) AND NVL(st.effective_end_date,ce.mapping_effective_date) AND cs.capplan_set_id in ( ? ? ? ? and fg.STAFF_GROUP_ID in ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? order by cs.CAPPLAN_SET_NAME,ce.capplan_edition_id desc, fs.site_name, st.staff_type_name ]
at org.hibernate.loader.custom.CustomLoader.getNamedParameterLocs(CustomLoader.java:379)
at org.hibernate.loader.Loader.bindNamedParameters(Loader.java:1769)
at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1704)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)

Environment

Production

Activity

Show:
Gail Badner
September 13, 2016, 4:56 AM

3.x and 4.x are no longer supported. Does this happen with 5.2?

Lasse Lindqvist
February 9, 2018, 10:21 AM

I have confirmed this issue with version 5.2.

The way it was reproduced was using deprecated SQLQuery like

SQLQuery sqlQuery = session.createQuery(SOME_STRING);
while (true) {
sqlQuery.addEntity(SomeEntityClass.class);
sqlQuery.setParameterList("someParameter", someParameter);
sqlQuery.list();

if (something) {
break;
}
}

So when we reuse an object of type SQLQuery we might get this error. Now, this might not be a bug if this kind of use is forbidden.

Gail Badner
February 10, 2018, 1:41 AM

SQLQuery is deprecated starting in 5.2, so I don't see this being fixed.

If org.hibernate.query.NativeQuery is affected, and if Hibernate supports reusing NativeQuery, then maybe it should be fixed.

, I don't think JPA says anything about supporting reuse of Query. Should Hibernate support that?

Assignee

Unassigned

Reporter

Nauman Bashir

Fix versions

None

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure