?
Solved

Commit Issue in Stored Procedure

Posted on 2008-10-26
6
Medium Priority
?
4,097 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

777 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