Menu
I'm working on Java application that integrates with legacy system written Oracle PL/SQL. Unfortunately i'm not able to change this legacy system. Problem with this system is that it that sometimes COMMIT statements are written into procedures. But this causes that I'm not able to handle transactions correctly on my application level.
So is it possible to make oracle database procedures to ignore commit statements?
I have found that when doing ALTER SESSION DISABLE COMMIT IN PROCEDURE in beginning of connection will cause exception when PL/SQL procedure is trying to commit. But is it possible to make Oracle to ignore commit without changing PL/SQL code?
421
A stored procedure may have a commit if there is a DML operation in the procedure and there is no restriction from Oracle's side that we cannot have commit or rollback in the procedure.
A commit should be used when you reach the end of a logical/business transaction i.e. when you want the data to be committed to the database. Some people seem to think that they should just commit after every DML statement, but this is bad practice and defeats the point of transactional applications.
"Oracle Database implicitly commits the current transaction before and after every DDL statement.". Oracle performs implicit commit before and after a DDL statement. So you don't have to perform a commit explicitly when it comes to DDL statement.
Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction. Note: Oracle recommends that you explicitly end transactions in application programs using either a COMMIT or ROLLBACK statement.
I don't think you can do that. You'll have to add a parameter to those procedures like"do commit"with a default value true. And you call them with parameter set to false. Pass the parameter value on, if they are nested. That way the legacy code still behaves the same but you get transaction control.
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
