How to call Funtion in Oracle?

sanjayiowa · **Joined:** Wed Aug 03, 2005 11:56 am **Posts:** 7

Hi,

How to execute function in Oracle 9.0 ? Given function would return int value.

Thanks

sanjayiowa · **Joined:** Wed Aug 03, 2005 11:56 am **Posts:** 7

Hi,

I have added named sql in the hibernate mapping file as follows:

Code:

<sql-query name="getSum" callable="true"> 
    <return-scalar column="sum" type="java.lang.Integer"/>
          {?=call getSum(?,?)}  
</sql-query> 

and my dao code looks like(Using Spring+Hibernate):

Code:

    Query query = getSession().getNamedQuery("getSum");
    query.setParameter(1,"16");
    query.setParameter(2,"10");
    Integer sum = (Integer) query.uniqueResult();

However I get following exception:

Code:

java.lang.ArrayStoreException
   at java.lang.System.arraycopy(Native Method)
   at java.util.ArrayList.toArray(Unknown Source)
   at org.hibernate.util.ArrayHelper.toTypeArray(ArrayHelper.java:75)
   at org.hibernate.impl.AbstractQueryImpl.typeArray(AbstractQueryImpl.java:627)
   at org.hibernate.impl.AbstractQueryImpl.getQueryParameters(AbstractQueryImpl.java:635)
   at org.hibernate.impl.SQLQueryImpl.getQueryParameters(SQLQueryImpl.java:161)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)

Just wonder if anyone has called Oracle Function using Hibernate 3.0

Thanks.

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

try simple jdbc - get jdbc connection from session and make callable statement

regards

sanjayiowa · **Joined:** Wed Aug 03, 2005 11:56 am **Posts:** 7

Thanks Snpesnpe. I was looking for more hibernate way to call Oracle Function but concluded with following code:

Code:

// get connection through SessionFactory & DataSource
SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
DataSource dataSource = SessionFactoryUtils.getDataSource(sessionFactory);
Connection connection = DataSourceUtils.getConnection(dataSource);
   
try{
      
   //  call the function
   CallableStatement cs = connection.prepareCall("{?=call getSum(?,?)}");
    
   // set type of return value
   cs.registerOutParameter(1, Types.INTEGER);
    
   // set input parameters
   cs.setString(2, 4);
   cs.setString(3, 4);
    
    // retrieve the result
    cs.execute();
    int res = cs.getInt(1);
      
}catch(Exception e){
   //log exception
}finally{      
  //close connection
  DataSourceUtils.releaseConnection(connection,dataSource);
}  

max · **Posted:** Sun Aug 28, 2005 3:21 pm

if you read the docs it explicitly states that when using callable=true the returned result must an resultset - yours are returning an integer...just use prepared statements as you did for that ;)

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

Code:

SessionFactory sessionFactory = getHibernateTemplate().getSessionFactory();
DataSource dataSource = SessionFactoryUtils.getDataSource(sessionFactory);
Connection connection = DataSourceUtils.getConnection(dataSource); 

you can call simple :

Code:

Connection connection = getHibernateTemplate().getSessionFactory().openSession.connection();

sanjayiowa · **Joined:** Wed Aug 03, 2005 11:56 am **Posts:** 7

I have refactored as :

Code:

Connection connection = getSession().connection();

Thanks.