Solved

Commit Issue in Stored Procedure

Posted on 2008-10-26
6
4,074 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:glimlach
6 Comments
 
LVL 9

Accepted Solution

by:
MarkusId earned 500 total points
ID: 22808175
The updating session (in this case the procedure) always sees all the changes done to the tables by itself, even when it afterwards selects directly from the table. So if you want the earlier stage of the updated record in the updating procedure after the update you'll need to query it before the update.
0
 
LVL 4

Expert Comment

by:Fekrat
ID: 22810413
You can do this on the sessions level with either of the two following:
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;
0
 
LVL 31

Expert Comment

by:awking00
ID: 22814887
Can you use flashback query in the "later steps"?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22818999
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now