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

[debasisdas]

You need to use PRAGMA AUTONOMOUS TRANSACTION.

[debi148]

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_TRAN SACTION 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.

A sample piece of my programme can be as below:
 
create table t1 (x number);
create table t2 (y number);
 
create or replace procedure t11
is
begin
insert into t1 values(1);
end;
/
 
create or replace procedure t12
is
begin
insert into t2 values(1);
end;
/
 
create or replace procedure t13
is
begin
insert into t1 values(2);  // Let's assume this returns 3 as we required
end;
/
 
declare
begin
t11();  -- I need to treat t12() and t13() as black boxes. I cannot modify that. 
         -- But I need to commit those at last. And rollback changes done by t11().
t12();
t13();
-- Need to have some arrangement built around this t12() and t13() to commit 
-- the changes. But changes by t11() need to be committed.
end;
/

Can you please suggest me how to use PRAGMA AUTO_TRANSACTIO N here or any other technique that I can use here.

Thanks again.
Debi

[debi148]

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.

A sample piece of my programme can be as below:
 
create table t1 (x number);
create table t2 (y number);
 
create or replace procedure t11
is
begin
insert into t1 values(1);
end;
/
 
create or replace procedure t12
is
begin
insert into t2 values(1);
end;
/
 
create or replace procedure t13
is
begin
insert into t1 values(2);  // Let's assume this returns 3 as we required
end;
/
 
declare
begin
t11();  -- I need to treat t12() and t13() as black boxes. I cannot modify that. 
         -- But I need to commit those at last. And rollback changes done by t11().
t12();
t13();
-- Need to have some arrangement built around this t12() and t13() to commit 
-- the changes. But changes by t11() need to be rolled back.
end;
/

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

[Saii]

Did you try using SAVEPOINT <name> and ROLLBACK to <name> statements in your code?

[debi148]

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

[amitpatel66]

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?