Solved

Commit Issue in Stored Procedure

Posted on 2008-10-26
6
4,078 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 32

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
Need a replacement data type in Oracle 6 65
Oracle 10g - insert string with special characters 8 41
MULTIPLE DATE QUERY 15 52
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

895 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

19 Experts available now in Live!

Get 1:1 Help Now