Oracle SQL | An interesting question from Valentin Nikotin

Test table:

create table tclob(c clob);

What is this code going to put out from the following blocks with and without “rollback”:

Task 1

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1:='1';
  insert into tclob values(cl1) returning c into cl2;
  cl3:=cl2;
  dbms_lob.append(cl3,'2');
  select c into cl4 from tclob;
--  rollback;
 
  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

[collapse]

Task 2

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '2');
  select c into cl4 from tclob;
--  rollback;
 
  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

[collapse]

Task 3

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '2');
  dbms_lob.append(cl3, '3');
  select c into cl4 from tclob;
--  rollback;
 
  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

[collapse]

Task 4

declare
  cl1 clob;
  cl2 clob;
  cl3 clob;
  cl4 clob;
begin
  cl1 := '1';
  insert into tclob values (cl1) returning c into cl2;
  cl3 := cl2;
  dbms_lob.append(cl2, '22');
  dbms_lob.append(cl3, '3');
  dbms_lob.append(cl2, '44');
  select c into cl4 from tclob;
--  rollback;
  dbms_output.put_line(cl1);
  dbms_output.put_line(cl2);
  dbms_output.put_line(cl3);
  dbms_output.put_line(cl4);
end;
/

[collapse]

Check it both in Windows and Solaris/Linux 🙂

Explanation
It is clear that this bug is platform-depending and that the matter is in specifics of working with memory. The answer lies in the fact that cl3 and cl2 have unsynchronized lengths, which means that Oracle “forgets” to change the lengths of all remaining variables, that point to this “clob”, and as every operation of changing cl2/cl3 in fact changes the same thing, it turns out that “superfluous” becomes overwritten.