Which statement is true about transactions in PL/SQL?
A. A transaction can span multiple blocks.
B. A block can contain only a single transaction.
C. SERVERPOINTS cannot be created in a PL/SQL block.
D. The END keyword signals the end of a PL/SQL block and automatically commits the transaction in the block.
Confirming correct answer is A.
Used following code to verify:
create table employees1 as select * from employees;
–Block 1
declare
l_count number;
begin
select count(*) into l_count from employees1;
dbms_output.put_line(‘Number of employees before transactions: ‘||l_count);
–Block 2
begin
–Transaction 1
execute immediate ‘insert into employees1 values (207,”Glen”,”Farmar”,”gfarmar”,”650.507.9822”,sysdate,”SH_CLERK”,3000,0,124,50)’;
commit;
–Transaction 1 finished.
–Transaction 2 started in same block
execute immediate ‘insert into employees1
values (208,”Mark”,”Jackson”,”mjackson”,”650.507.9822”,sysdate,”SH_CLERK”,3000,0,124,50)’;
end;
–Block 3
begin
–Transaction 2 continued in other block
execute immediate ‘insert into employees1
values (209,”Chris”,”Paul”,”cpaul”,”650.507.9822”,sysdate,”SH_CLERK”,3000,0,124,50)’;
SAVEPOINT A;
select count(*) into l_count from employees1;
dbms_output.put_line(‘Number of employees before end keyword: ‘||l_count);
–Testing if end keywork will commit, otherwise, rollback will occur next.
end;
rollback;
select count(*) into l_count from employees1;
dbms_output.put_line(‘Number of employees after rollback: ‘||l_count);
end;
/
option C – SERVERPOINTS or Savepoints