Hi,

I am working on using Hibernate on a legacy db system, which uses stored functions in Oracle. I tried to invoke one of these functions by using the formula attribute in property as the function simply returns a varchar so i was hoping that Hibernate would pass execution to Oracle which would just return the varchar and Hibernate would not know any better.

However, it fails when i call it giving me an "ORA-00904 - invalid identifer" error. When i run the executed query in SQLNavigator it runs ok.

And it definitely seems to be an issue with stored functions being executed as i substitued it with a dummy sum function and it ran ok.

So why does Hibernate fail on executing this? And is there a way around it?

Thanks,
John

Hibernate version: 3.2.4.sp1

Mapping documents:

<property name="linkedAls"
type="string"
formula="(select getlinkedALs( la.LICENSE ) from La_Licenses la where la.LICENSE = LICENSE)" />

<!--

<property name="linkedAls"
type="string"
formula="(select sum(la.APPLICATION_NO) from La_Licenses la where la.LICENSE = LICENSE)" />

-->

Full stack trace of any exception that occurs:

2007-06-29 09:06:18,468 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 904, SQLState: 42000
2007-06-29 09:06:18,468 ERROR [org.hibernate.util.JDBCExceptionReporter] - ORA-00904: : invalid identifier

2007-06-29 09:06:18,500 ERROR [org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/wis].[wis]] - Servlet.service() for servlet wis threw exception
java.sql.SQLException: ORA-00904: : invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)

Name and version of the database you are using: Oracle 10g - 10.2.0.3.0

The generated SQL (show_sql=true):

select distinct licenses0_.LICENSE as LICENSE32_0_,
licenses0_.APPLICATION_NO as APPLICA59_32_0_,
(select getlinkedALs( la.LICENSE ) from La_Licenses la where la.LICENSE = licenses0_.LICENSE) as formula0_0_
from LA.LA_LICENSES licenses0_
left outer join DLWC.DLWC_ACCOUNT_ROLES relatedacc1_
on licenses0_.DLWC_ACCOUNT_ID=relatedacc1_.DLWC_ACCOUNT_ID
where (licenses0_.LICENSE like 'SOME_VAL')
and (relatedacc1_.VALID_TO is null)
and (relatedacc1_.ROLE_TYPE in ('HO' ,'HA' ,'OW'))
and relatedacc1_.ROW_EXPIRES=to_date('01013000','DDMMYYYY')

Also, I tried to just execute this command as plain sql, not pretty but if it got me around the problem, it'd have to do.

So when i get a list of the record objects, i then loop through this list to set the individual property.

However, executing this as plain sql still gives me the same error, even though if you take the generated sql out and run it in SQL Navigator, it runs fine.

And even if i just call the list() method, ignoring actually retrieving the value in Java, it still fails.

Why is this does anyone know? Am i missing something here?!

Thanks,
John

List licenses = getHibernateTemplate().
findByNamedQuery("getLicenseInfoByLicenseNumWildCard", new Object[]{licenceNumber});

logger.info("num licences found=["+licenses.size()+"]");

String sql = "select getlinkedALs( la.LICENSE ) as linkedAls from La_Licenses la where la.LICENSE = :LicenseNum";
Session sess = getHibernateTemplate().getSessionFactory().getCurrentSession();

for(int i = 0; i < licenses.size(); i++){
Licenses licence = (Licenses)licenses.get(i);
logger.info("licence num=["+licence.getLicense()+"]");

Query q = sess.createSQLQuery(sql);
//Query q = sess.createSQLQuery(sql).addScalar("linkedAls", Hibernate.STRING);
q.setString("LicenseNum", licenceNumber);
//String linkedAls = (String)q.uniqueResult();
q.list();
//logger.info("linkedAls=["+linkedAls+"]");
//licence.setLinkedAls(linkedAls);

}

Got the function to execute and return using the sql-query as below:

<sql-query name="getlinkedALs">
<return-scalar column="linkedAls" type="string"/>
select getlinkedALs(:licenseNum) linkedAls from dual
</sql-query>

String linkedAls = (String)sess.getNamedQuery("getlinkedALs").setParameter("licenseNum", licenceNumber).uniqueResult();
logger.info("linkedAls=["+linkedAls+"]");
licence.setLinkedAls(linkedAls);

But this is still not ideal as it requires an extra sql statement per object found in parent query, which may be in the hundreds, therefore resulting in hundreds of extra sql statements to retrieve this val.

This really isn't ideal, does anyone know if we can execute a stored function through the formula attribute of a class property.

This would be very handy...

Fixed it!!

Applied same sql format as displayed in previous quote to the function.

<property name="linkedAls"
type="string"
formula="(select getlinkedALs( LICENSE ) from dual)" />

Not sure what the problem here was but Hibernate doesn't seem to like the format:

<property name="linkedAls"
type="string"
formula="(select getlinkedALs( la.LICENSE ) from La_Licenses la where la.LICENSE = LICENSE)" />


Just select val from dual and it will work ok.

Hopefully this might help others with similar probs.

John