Hibernate version: 3.1.2

Mapping documents:
<hibernate-mapping package="com.dao.demo">
<class name="Words" table="Words">
<id name="id" type="java.lang.Long">
<column name="id" length="11" not-null="true" unique="true" sql-type="number" />
<generator class="increment" />
</id>

<property column="DESCRIPTION" length="1000" name="description" not-null="true" type="string" />
<property column="WORD" length="50" name="word" not-null="true" type="string" />

<sql-insert callable="true">
{call pp.P_Insert_BanWord (?,?,?)}
</sql-insert>
</class>
</hibernate-mapping>


Name and version of the database you are using:oracle 10 g

The generated SQL (show_sql=true):yes

Debug level Hibernate log excerpt: info

I use 'sequence ' as my id generator, and when object words is saved ,it cost two connection. And the max connection of my db connection pool is 150.
When my system is busy, it ofen logs like this:
Cannot get a connection, pool exhausted
And the DBA of my system told me I shold save object within one connection to improve the performence of my system.

So, I think maybe I can use store procedure of oracle to solve my problem. And I write a store procedure like this:
CREATE OR REPLACE PROCEDURE "WORDS" (
p_word in varchar2,
p_description in varchar2,
p_temp_id in out number)
as
begin
insert into words values (
seq_words_id.nextval,
p_word,
p_description)
as
begin
insert into words values (
seq_words_id.nextval,
p_word,
p_description);
commit;
select seq_words_id.currval into p_temp_id from dual;
end;
/

and I use p_temp_id as my out parameter, I hope hibernate can get the return value as the true id for my system.

But i failed.

First my system is a cluster system, so I can't use "increment" as my id generator.
Next hibernate can't get the return value of my procedure as true object id value.

Just what shall I do if I want to improve the performence of my system. I want to cut down the db connection numbers from my system to db.
Thanx.

I don't understand your problem. What do you do and what don't you manage to do ?

As I understand, you use your stored procedure to create words objects. Then, you load it using Hibernate ?