Hi All, @hanson
I want to commit a transaction without committing the main block because main block may have several validations and based on the validation result, we may want to rollback the main block.
However we still want to commit the inner transaction.
I was trying this…
insert into arr1 values (1, “udit”);
insert into arr2 values (2, “khemka”);
create or replace procedure seperate_transaction() returns VARCHAR(50) as
declare
message VARCHAR(50);
begin
update arr1 set name = ‘john’ where id = 1; – first statement
message = seperate_transaction_inner(); – calling inner transaction
rollback; – This should rollback the first statement but inner transaction should be committed.
return “success rollback”;
end;
/
create or replace procedure seperate_transaction_inner() returns VARCHAR(50) as
begin
insert into table1 values(2);
start transaction;
update arr2 set name = ‘smith’ where id = 2;
commit;
return “success commit”;
end ;
/
call seperate_transaction();
When I check arr1 and arr2 tables, data is commited in both the tables…
What is the solution to this issue?
Regards
Himanshu