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

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():


net.sf.hibernate.QueryException: in expected:
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)


Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hi,

I am trying to write an HQL query combined with Oracle function.

This is how the sql query looks like:
select * from my_table where my_table.columnA = 1 and my_table.columnB IN ( select * from table(my_function()) )

my_function just returns an array. Could anyone help me as to how to write this in HQL. Thanks a lot

You can just call your function inside of the HQL. It has been my experience that if hibernate does not recognize something as an object or property of an object in the query, it will leave it as is.

Something like this should work:




I have not tried to do anything with an array like you are doing, but this may work:



Hope that helps.

Paul

HI,

Thanks for your help, I still didn't get it to work.

Previously I have tried what you suggested
from myobject as analias where
analias.columnA = 1 and analias.columnB IN(my_function())

but this throws a JDBC exception.
ORA-00932: inconsistent datatypes: expected NUMBER got IDS_ARRAY

I know that the end of the sql query should look something like this
select * from table(my_function()) )
as this returns a valid response but I have not been able to transform in into HQL.

Have you tried it just the way you explained to me?

from myobject as analias where
analias.columnA = 1 and analias.columnB IN( select * from table(my_function()))

Hi,

Yes I have but it is not able to parse HQL into SQL. I get an exception

in expected:
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)

I fail to see where "in" should be expected.

Thanks a lot for your help, appreciate it.

maybe its because of the select * on the inner query. Can you try a diferent query selecting 1 column w/out the function just to see if it is the * that it does not like?

Have you tried this: Select my_function()???





Paul