Share transaction between java and PL/SQL

[prinSpring]

Hi All,

I have an application that use spring ORM integration with Hibernate to access to database and I have some stored procedure in oracle.

In the same business case i can invoke hibernate and a stored procedure, I want execute both the hibernate operations(insert, update, delete, ...) and the stored procedure in same transaction scope.

If the stored procedure fail, roolback hibernate operations, and if hibernate operations fail rollback the modification done by the stored procedure.

Any Idea how can i share my transaction that opened in java by Spring AOP with PL/SQL stored procedure?

If you want more detail i can explain more.
Thanks for your help.

[gwa]

You can call you stored procedure with hibernate. Just declare the procedure as a named query and execute in your transaction.

[abukhalique]

I have a similar problem with transaction and stored proc.

@transactional are declared for dao methods as well as service. Autocommit for datasource is set to true. There is no requres new.

in the business method there are bunch of inserts using jdbc template. then a stored procedure does bunch of inserts in the same table. at the end stored procedure sends count of total insert. Seems like JDBC inserts are not visible to storeproc. Storeproc does not have any commit or rollback in PL/SQL code.

If runtime exceptions is thrown at the very end of the service method only jdbc sql inserts are rolledback.

if autocommit is set to false storeproc inserts do not commit.

Datablse is Oracle. Application is a java app ( not deployed in server )

[prinSpring]

I have two ideas, but i must test it

The first make a transaction on the connexion for a datasource
The second open the transaction in the java scope and send the ID of the transaction to the stored procedure.

What do you think?

[abukhalique]

Thanks prinSpring.

Solved the problem using spring StoredProcedure passing the JdbcTamplate during object creation.
.
Lesson learned: Do not mix stored-procedure through vanilla JDBC in Spring Transaction. Use Spring StoredProcedure instead.