Solved

Commit Issue in Stored Procedure

Posted on 2008-10-26
6
4,087 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
date show only hh:mm 2 39
oracle query 3 26
error starting form builder in 11g 2 26
Oracle perfomance issue. 4 21
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

733 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