executing functions from database oracle

piltrafeta · **Joined:** Wed Apr 23, 2008 2:00 pm **Posts:** 20

Hello!
I'm trying executing a function whichs returns me a boolean from my Oracle DB and i'm having some problems.
My mapping definition is like this :

<sql-query name="setTransactions" callable="true">
{? = call MyPackage.myFunction (?,?) }
</sql-query>

and in my java code i'm doing like this :
Session session = HibernateUtil.getSession();
List list = session.getNamedQuery("setTransactions")
.setInteger(0, idTr)
.setString(1, nroContenedor)
.list();
session.close();

When i execute i'm having

ADVERTENCIA: SQL Error: 6550, SQLState: 65000
09/06/2008 10:49:22 org.hibernate.util.JDBCExceptionReporter logExceptions
GRAVE: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
...

Does somebody can help me ?
Thanks !

Cameron McKenzie · **Posted:** Mon Jun 09, 2008 10:33 am

Code:

 .setInteger(0, idTr)
.setString(1, nroContenedor) 

I think you might want to look at the datatypes of idTr and nroContenedor. I don't think they're Integer and String objects.

piltrafeta · **Joined:** Wed Apr 23, 2008 2:00 pm **Posts:** 20

actually i found the error. The function was returning a boolean, but in the documentation says that for oracle the functions/procedures must return a ResultSet.
So with this now is working !
Thanks !

piltrafeta · **Joined:** Wed Apr 23, 2008 2:00 pm **Posts:** 20

i'm having another problem...
when i get the ResultSet from the DB, i'm getting null values whilke i should get a string values...
How i have to configurate my mapping to do this ?
This is how i'm doing now :

<sql-query name="setTransactions" callable="true">
<return class="java.lang.String" />
{ ? = call myPackage.MyFunction(?,?) }
</sql-query>

This gives me an error for the <return class="java.lang.String" />
If i delete it, there is no error but i'm getting null values as a result of my query.