Hey there. We are trying to re-write an application using Hibernate/Spring/JSF. This is a legacy app where a majority of the logic resides inside of the database as stored procedures. As part of our application, we have a stored procedure that does some logging, system maintainance, and puts a row into a table. This stored procedure doesn't return a value. From reading the documentation, it's pretty clear that when calling a stored procedure you need to have the first variable in a stored procedure be an OUT variable. I wrote a wrapper function around the stored procedure to return a sys_refcursor (via the hibernate docs). I am just assuming that the error that is getting thrown below is because I don't have a return value mapped.

When I was trying to call the original stored procedure without a return value I was getting what appeared to be 'off by one' errors (I would have two variables, would call setParameter(0,"a") and setParameter(1,"foo") and would receive the error "cannot set parameter:3").

My question is how do I map this return value? It's essentially a dummy value just to get this to work.. it doesn't map to a table or any type in our system. I've been haggling with this for two days now and don't see a concrete example of how to use it.

Thanks in advance!



Hibernate version:
3.2.0

Mapping documents:



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



Full stack trace of any exception that occurs:



Name and version of the database you are using:
Oracle 10G

if you don't need the return value then why don't you either

a) use .executeUpdate()

or

b) just use a normal jdbc connection to do the call ?

The short answer is because I am new to hibernate.

Thanks a million!! After a little tinkering, that worked!

Hate to perform thread necromancy here, but doesn't .executeUpdate() throw a

Exception in thread "main" java.lang.IllegalArgumentException: callable not yet supported for native queries

in this situation?

not if you use an uptodate version of hibernate

What is the uptodate version You're referring to?

In 3.2.5.ga source NativeSQLQueryPlan

I stand corrected - it only works for call syntax on dbs that allow it for prepared statemetns.

I believe that is how I got the result for a record count query. If the oracle function is returning a resultset. Use code as below.



then you can use the resultset as you please.

Cheers!

yup - that is the hack we use to support callable for oracle.

Since you are using Spring, have you consider using the StoredProcedure Wrapper that Spring provides?

http://static.springframework.org/sprin ... edure.html

I've used it before and was pretty straight forward.

Do you think you could post a simple example please?

Well, first this would be a Spring answer (I apologize in advanced).

Most of the code I wrote is proprietary information, however most of what I wrote is found at Spring documentation (which has a pretty decent information). They also have a good forum where you can search for Stored Procedures.

http://static.springframework.org/sprin ... dProcedure

You should end up with something like (this is code and pseudocode).

public MyStoredProcedure extends StoredProcedure {
public Bar(JdbcTemplate template, String name) {
super(template, name);
}

// more code as you need it ...
}

public FooDao extends JdbcTemplate {

public Object executeStoreProcedureOne(String value1, ...) {

MyStoredProcedure sp = new MyStoredProcedure(getJdbcTemplate(), "<name_of_your_sp>");

// follow the Stored Procedure API
// Map allParameters = new .... (build parameters)
// sp.declareParameters
// sp.execute(allParameters);
}
}

Needless to say that if you use this approach, you need to make this JdbcTemplate take part of your transaction manager (which you may or may not be familiar with). Spring forums can help you a lot more with this (this is hardly a Hibernate question).

I have a problem with this code let me see:

private void listaInfoUser(String arg1){
Session session;
session = sessionFactory.openSession();
CallableStatement st;
try {
st = session.connection().prepareCall("{? = call selectTestInfoUser_sp(?)}");
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setString(1, arg1);
st.execute();

} catch (HibernateException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}

and the error display:

java.sql.SQLException: Falta el parámetro IN o OUT en el índice:: 2
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1844)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3311)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
at mx.uam.hibernate.ManejadorUser.listaInfoUser(ManejadorUser.java:38)
at mx.uam.hibernate.ManejadorUser.main(ManejadorUser.java:50)

What´s happen?