Commit Issue in Stored Procedure
Hi,
I have a weird problem. I have a Java program that is getting a Connection, setting the autocommit to false, Executing a stored procedure in which I am updating a record. And in later steps in the same procedure, I am querying for the same record and I am getting updated record where as I need old record (before updating the record). I still did not do any commit operations but I am still getting the updated record which is not required and giving me a problem.
How stored procedure commits when the operation is in progress? I am using TOAD8.6.1 and with a setting autocommit is false for that tool. I am using oracle10g.
What should i do in my stored procedure if i want autocommit to be false till the end of my procedure?
Appriciate your early response
Thank You
I have a weird problem. I have a Java program that is getting a Connection, setting the autocommit to false, Executing a stored procedure in which I am updating a record. And in later steps in the same procedure, I am querying for the same record and I am getting updated record where as I need old record (before updating the record). I still did not do any commit operations but I am still getting the updated record which is not required and giving me a problem.
How stored procedure commits when the operation is in progress? I am using TOAD8.6.1 and with a setting autocommit is false for that tool. I am using oracle10g.
What should i do in my stored procedure if i want autocommit to be false till the end of my procedure?
Appriciate your early response
Thank You
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you use flashback query in the "later steps"?
It is not a weird problem. It is the normal behaviour. Your stored procedure is working in an "oracle session". Any changes you make inside that session will be visible to the same session. Auto commit settings(i.e. commit) decide whether your changes are visible to other sessions. If you dont commit; other sessions sees the data prior to modification. If you commit; other sessions see the changed data.
Now, within your stored procedure; if you want to operate on the previous data; the simple solution will be to copy the data into local variables.
Now, within your stored procedure; if you want to operate on the previous data; the simple solution will be to copy the data into local variables.
alter session disable commit;
--or by executing the stored procedure:
SQL > alter session enable commit in procedure; or
SQL > alter session disable commit in procedure;