I am trying to perform a query based on the concatenation of two string fields. The data I have at the time of the query is a single value which is the concatenation of these two fields. I have no way to know how to break up that value to perform the query of the two fields individually. I tried the query string using both || and concat() which produced the same error and stack trace below. Thanks in advance for any information.


Hibernate version:
3

Mapping documents:
I am using Oracle9Dialect
From my hibernate.cfg.xml:
<property name="dialect">
org.hibernate.dialect.Oracle9Dialect
</property>

Code between sessionFactory.openSession() and session.close():
String sql = "select {test*} from BCT_TEST_SET_NUM_T test where " +
"concat({test.Prefix},{test.comp_id.PhoneNum}) = :num";
List resList = sess.createSQLQuery(sql)
.addEntity("test", BctTestSetNumT.class)
.setString("num", prefixPhoneNum)
.list();

Full stack trace of any exception that occurs:
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1525)
at org.hibernate.loader.Loader.list(Loader.java:1505)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1343)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
at net.idt.bct.bl.BctLoadResultsJob.execute(BctLoadResultsJob.java:179)
at org.quartz.core.JobRunShell.run(JobRunShell.java:191)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:516)
Caused by: java.sql.SQLException: Non supported SQL92 token at position: 12: test
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:861)
at oracle.jdbc.driver.OracleSql.handleToken(OracleSql.java:681)
at oracle.jdbc.driver.OracleSql.handleODBC(OracleSql.java:595)
at oracle.jdbc.driver.OracleSql.parse(OracleSql.java:531)
at oracle.jdbc.driver.OracleSql.getSql(OracleSql.java:270)
at oracle.jdbc.driver.OracleSql.getSqlBytes(OracleSql.java:296)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:166)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:894)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:984)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2885)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2926)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:124)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1204)
at org.hibernate.loader.Loader.doQuery(Loader.java:368)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:208)
at org.hibernate.loader.Loader.doList(Loader.java:1522)
... 7 more

Name and version of the database you are using:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

The generated SQL (show_sql=true):
Hibernate: select {test*} from BCT_TEST_SET_NUM_T test where concat(PREFIX5_0_,PHONE2_0_) = ?

Debug level Hibernate log excerpt:

You do not seem to have dot between test and asterics therefore H did not expanded alias "test".

Try select {test.*} and make sure that generated SQL is valid, right now it is not.