Solved

Commit Issue in Stored Procedure

Posted on 2008-10-26
6
4,082 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
C# Connection String for Oracle database is not working 22 153
Oracle and DateTime math 6 39
oracle 11g 23 87
Oracle - Query link database loop 8 39
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

825 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