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

asked 07 Oct '22, 04:32

Baron's gravatar image

Baron
2.0k125138165
accept rate: 48%


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;
permanent link

answered 07 Oct '22, 05:08

Volker%20Barth's gravatar image

Volker Barth
39.8k358546815
accept rate: 34%

edited 07 Oct '22, 05:10

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);


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(); insert into tmpemployees values (emp, sal); end;


create or replace procedure proc2_2() begin insert into tmpemployees select * from proc1(); end;


select * from proc2_1()--OK select * from proc2_2()--commit/rollback not allowed within atomic operation

(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×27
×8
×5

question asked: 07 Oct '22, 04:32

question was seen: 280 times

last updated: 07 Oct '22, 09:41