Calling stored procedures

[Saravanan Subramani]

Hi ,

I am new in hibernate. What i want to do is, I have to call a procedure.
without specifying the column names in mapping file.

My procedure accept 2 input parameters, it generate a unique number and return it as out parameter. In this scenario,i dont know How to map and how to write the code.

this is my procedure,

FUNCTION FETCHID(ID VARCHAR,LABEL VARCHAR) RETURN NUMBER IS
OUTID NUMBER;
BEGIN

select swith_id into OUTBATCHID from ps_async
where h1sd_xb_exec_oprid = ID and trunc(send_date) = trunc(sysdate);

RETURN OUTID;
exception
when no_data_found then
select h1sd_xb_batch_seq.nextval into OUTID from dual;
insert into ps_async values(ID,sysdate,OUTID);
commit;
insert into xb_batch values (OUTID,INLABEL,'NEW',ID,' ',0,trunc(sysdate),ID,trunc(sysdate),ID);
commit;
RETURN OUTID;
END;

My procdure is referring 2 table. I saw so many samples like

<sql-query name="selectAllEmployees_SP" callable="true">
<return alias="emp" class="employee">
<return-property name="empid" column="EMP_ID"/>

<return-property name="name" column="EMP_NAME"/>
<return-property name="address" column="EMP_ADDRESS"/>
{ ? = call selectAllEmployees(?,?) }
</return>
</sql-query>

I think these are not needed in my requirement.what i want to do is just send 2 string parameters and get out parameter. i dont know how to set return-property for my requirement.

Please suggest me how to do that,

Thanks in advance
Saravanan.
[ July 17, 2008: Message edited by: ssaravanan saravanan ]

[Joe Matthew]

Though not recommended, use a cursor to get the result set you require:

Code:
CallableStatement callableStatement = null;

callableStatement = session.connection().prepareCall("Your SP name");

callableStatement.setString(1, "aaaa");

callableStatement.registerOutParameter(2,OracleTypes.CURSOR);

callableStatement.execute();

ResultSet rs = (ResultSet)callableStatement.getObject(2)

Thanks,
Joe
[ June 19, 2008: Message edited by: Joe Mathew ]

[Saravanan Subramani]

Thanks for reply.
I used mapping with following way,
//mapping
<sql-query name="FETCHBATCHID" callable="fale">
{ call PSFTCTE.common.TASKID(?) }
</sql-query>

//code
Query query = session.getNamedQuery("FETCHBATCHID").setParameter(0, userId);
int batId = query.executeUpdate();

i changed callable="false" in mapping file.

procedure is executing finely.but another problem is, when i call a procedure that return a out parameter, i am getting error.

mapping like this,

<sql-query name="FETCHBATCHID" callable="false">
{? = call PSFTCTE.xmlbatch_common.FETCHTASKID(?,?) }
</sql-query>

//code
Query query = session.getNamedQuery("FETCHBATCHID").setParameter(0, userId).setParameter(1, label);
int batId = query.executeUpdate();

I don't know how to write for get retrun out parameter.

Following Error is throwing ORA-01008: not all variables bound)

Hibernate: {? = call PSFTCTE.xmlbatch_common.FETCHTASKID(?,?) }
org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:174)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1163)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:334)
at com.orderengine.logic.TestDatabase.createBatchId(TestDatabase.java:100)
at com.orderengine.logic.TestDatabase.main(TestDatabase.java:36)
Caused by: java.sql.SQLException: ORA-01008: not all variables bound

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:165)
... 4 more



I can use what you suggesed.But i would like to know why my code is not working when there is a return out parameter & where i made mistake.

Thanks in advance..

[Saravanan Subramani]

Hi
My procedure will be return a integer object. not like a row.

i have problems with the return parameters of the stored procedure.

Thanks, I appreciate any help.

[Joe Matthew]

are you registering the out variable before the SP is executed?

[Saravanan Subramani]

Hi,
Thanks for reply,

I dont know how to register the out variables before the SP is executed

Thanks.

[Joe Matthew]

LinkThis will help you out.

But the previous post solution will help does not have any constraints other than it uses a deprecated method.
[ June 25, 2008: Message edited by: Joe Mathew ]