473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Can I commit portion of transaction in Oracle 9i?

debi148
Hi,

I am using Oracle 9i. I am using DML's only. Here I am inserting and updating some records upto say savepoint a. Then I insert and/or update few more records say upto savepoint b. The savepoint b returns some error like, 0, 1, 2, 3.

My requirement is I need to check the error. If it is 0, I need to commit from start to savepoint b.

If it is 1 or 2, I need to rollback from start to savepoint b.

But if it is 3, I need to rollback only from start to savepoint a. But the portion of transactions from savepoint a to savepoint b need to be committed.

I am not able to think how to acomplish the third condiotion, i.e, when the error type is 3.

Can anyone help me regarding this. Thanks a lot in advance.

Debi
Apr 24 '08 #1
6 2495
debasisdas
8,127 Expert 4TB
You need to use PRAGMA AUTONOMOUS TRANSACTION.
Apr 25 '08 #2
You need to use PRAGMA AUTONOMOUS TRANSACTION.
Hi Sir,

Thank you very much for your help and the piece of inforamtion. But I need a bit more help regarding my problem.

What I understood from other sources is PRAGMA AUTONOMOUS_TRANSACTION can be applied at single procedure level.

But for me the portion that I need to commit is in terms of multiple stored procedures. And since multiple processes access that, I cannot touch those procedures. But for return type 3, the multiple blocks of stored procedures need to be commited at one shot. and for return type 1, 2 it should be rolled back.

Can I apply this child transaction concept as a single entity to a set of procedures.


Expand|Select|Wrap|Line Numbers
  1. A sample piece of my programme can be as below:
  2.  
  3. create table t1 (x number);
  4. create table t2 (y number);
  5.  
  6. create or replace procedure t11
  7. is
  8. begin
  9. insert into t1 values(1);
  10. end;
  11. /
  12.  
  13. create or replace procedure t12
  14. is
  15. begin
  16. insert into t2 values(1);
  17. end;
  18. /
  19.  
  20. create or replace procedure t13
  21. is
  22. begin
  23. insert into t1 values(2);  // Let's assume this returns 3 as we required
  24. end;
  25. /
  26.  
  27. declare
  28. begin
  29. t11();  -- I need to treat t12() and t13() as black boxes. I cannot modify that. 
  30.          -- But I need to commit those at last. And rollback changes done by t11().
  31. t12();
  32. t13();
  33. -- Need to have some arrangement built around this t12() and t13() to commit 
  34. -- the changes. But changes by t11() need to be committed.
  35. end;
  36. /
  37.  
Can you please suggest me how to use PRAGMA AUTO_TRANSACTION here or any other technique that I can use here.

Thanks again.
Debi
Apr 25 '08 #3
Hi All,

I am very sorry to everyone who read or is reading the above piece od code snippet provided by me.

At line 34, by mistake I have updated the wrong statement. The right one is
-- Need to have some arrangement built around this t12() and t13() to commit
-- the changes. But changes by t11() need to be rolled back.

So the code snippet looks as below.
Expand|Select|Wrap|Line Numbers
  1. A sample piece of my programme can be as below:
  2.  
  3. create table t1 (x number);
  4. create table t2 (y number);
  5.  
  6. create or replace procedure t11
  7. is
  8. begin
  9. insert into t1 values(1);
  10. end;
  11. /
  12.  
  13. create or replace procedure t12
  14. is
  15. begin
  16. insert into t2 values(1);
  17. end;
  18. /
  19.  
  20. create or replace procedure t13
  21. is
  22. begin
  23. insert into t1 values(2);  // Let's assume this returns 3 as we required
  24. end;
  25. /
  26.  
  27. declare
  28. begin
  29. t11();  -- I need to treat t12() and t13() as black boxes. I cannot modify that. 
  30.          -- But I need to commit those at last. And rollback changes done by t11().
  31. t12();
  32. t13();
  33. -- Need to have some arrangement built around this t12() and t13() to commit 
  34. -- the changes. But changes by t11() need to be rolled back.
  35. end;
  36. /
  37.  
Once again I am very sorry for the confusion.

If anybody I have any idea to get it done, that will be of great help. Thanks a lot in advance.
Debi
Apr 28 '08 #4
Saii
145 Expert 100+
Did you try using SAVEPOINT <name> and ROLLBACK to <name> statements in your code?
May 9 '08 #5
Did you try using SAVEPOINT <name> and ROLLBACK to <name> statements in your code?
Saai,

Thanks for your interest in this problem.

I tried savepoint and rollback, but could not think of a way to achieve a solution to the problem here.

The condition is as below.
Savepoint A; some code
Savepoint B; some code;
Savepoint C;
Now commit rollback from Savepoint A to B and commit from B to C.

Please suggest me if you can think any logic to achieve this solution.

Thanks a lot.
Debi
May 12 '08 #6
amitpatel66
2,367 Expert 2GB
Saai,

Thanks for your interest in this problem.

I tried savepoint and rollback, but could not think of a way to achieve a solution to the problem here.

The condition is as below.
Savepoint A; some code
Savepoint B; some code;
Savepoint C;
Now commit rollback from Savepoint A to B and commit from B to C.

Please suggest me if you can think any logic to achieve this solution.

Thanks a lot.
Debi
Are you looking at commiting the transactions that happened between savepoint B and C and roll back the transactions that happened between save point A and B?
May 13 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: DP | last post by:
Is there any command exists as "commit transaction" ? begin transaction < DML1 stmts > begin transaction < DML2 stmts > rollback transaction commit transaction which set of DML will be...
1
by: anders_tung | last post by:
Hi, I have a procedure which will call 3 functions. First function will update a record. Second function will delete a record. Third function will insert a record. Each function will return...
11
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to...
0
by: Fan Ruo Xin | last post by:
Nothing got wrong with DB2 COMMIT, db2 cfg, ... Even you did an "insert some_id+1 ..." immediately after you did "select max(some_id) from .... " in session1. This will not block the operations...
1
by: rajamohammed | last post by:
Hi, I using Oracle Database connection in Java and I run "DELETE" sql query . In oracle, we will use commit command after every transaction. Please tell me, How can I use commit in Java? Should...
1
by: drewbob | last post by:
Hello! I'm relatively new to oracle, so i hope I use the correct terminology. Please let me know if my question doesn't make sense! I'm trying to figure out how to turn off the session level...
3
by: Anonymous | last post by:
I resolved this issue. If you are using an Oracle XA DataSource, Oracle's JDBC driver will automatically issue a COMMIT when it sees a "DROP TABLE foobar" statement. Oracle's JDBC driver...
4
by: Shaila24072009 | last post by:
We are getting error 'Root transaction wanted to commit but transaction aborted' when we try to insert huge data into oracle database from the application. We are using Windows Server 2003, service...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.