|
An atomic operation means that an the operation is not dismountable, so it will be either completely executed or not at all. A temporary not transactional table means that its contents will not be written in transaction log files, so that it will be treated as variable. Can anyone tell me why I can't execute proc2_2 below? create or replace table employees (empname varchar(100), salary double);
insert into employees values ('Tom', 130);
---------
create or replace procedure proc1 ()
begin
commit;--proc2_2 doesnt like this commit
select * from employees;
end;
-------
create or replace procedure proc2_1()
begin
declare emp varchar(100);
declare sal double;
select * into emp, sal from proc1();
select emp, sal;
end;
-------
create or replace procedure proc2_2()
begin
declare local temporary table tmpemployees (emp varchar(100), sal double) not transactional;
insert into tmpemployees select * from proc1();
select * from tmpemployees;
end;
-------
select * from proc2_1()--OK
select * from proc2_2()--commit/rollback not allowed within atomic operation
|
|
This has nothing to do with the NOT TRANSACTIONAL clause of your local temporary table. If you omit that clause, the error arises, too. IMHO, basically you are calling an atomic operation (INSERT) based on a SELECT that does a commit internally via proc1. That simply violates the atomicity. There cannot be a commit while INSERT is executing. In contrast, you can start and commit savepoints (aka sub-transactions) within your procedure, such as create or replace procedure proc1 () begin savepoint MySavepopint; select * from employees; release savepoint MySavepopint; end; Yes, you have right, it has nothing to do with NOT TRANSACTIONAL. Should I understand that the statement insert into .... select is an atomic operation? Here I have also the same problem: create or replace table employees (empname varchar(100), salary double);
create or replace table tmpemployees (empname varchar(100), salary double);
insert into employees values ('Tom', 130);
(07 Oct '22, 09:22)
Baron
Replies hidden
Well, an INSERT statement is an atomic operation, like UPDATE, DELETE and MERGE. And INSERT...SELECT is just one of its variants, just like INSERT...VALUES. It does not matter for atomicity whether you supply the new values via VALUES or via the result set of a SELECT, it's still the same atomic operation. I guess it's even a more general rule: EACH non-compund SQL statement is atomic.
(07 Oct '22, 09:25)
Volker Barth
|
