Can some one tell me how to call an Oracle Stored procedure having these parameters :

PROCEDURE ps_TypeApa (
o_curs OUT pck_entenial_b2b.TYP_CURS_LISTE_APA_AGENT,
i_abt_id IN TABONNEMENT.ABT_ID%TYPE)

Thank you

Use a direct JDBC connection, you can obtain one from session.connection()

warning: if your strored procedure update records, the session will not be uptodate with the updates objects !!
If you want to avoid the problem:
1- get a session
2- do what you have to do with your objects
3- flush & commit
4- session.clear
5- session.getConnection --> you need this to call your strored procedure
6- after this you can reuse your session

Note that if your strored procedure return "rows" and you want to use the rows as "business objects" by using the setters, you're going into troubles...

HQL let you call complexe objects queries, and if you use HQL you don't have to worry about your object's persistence.
But if you need to use a strored procedure to return a "very complex" query, this only mean that your domain model or your database model is wrong....

How would other sessions react to this change?

IE - isn't there an issue arising from the absence of any form of locking between another session and the stored procedure?

Session 1 loads object X,

Session 2 behaves as per the above description and alters the data for X.

Session 1 works on X and saves updates to the database. Surely this would overwrite any changes made by session 2?

and what happens if session2 delete X? you're going into troubles

One of the nice things about Hibernate is that it relies almost completely upon the database for locking concerns. So no, there is no problem with this approach. Your database will throw an exception if you are in repeatable read or above. Otherwise, if you are in only read committed, you can use a version or timstamp column and Hibernate will detect an optimistic lock failure.

Easy.