Oracle SQL
  • LICENSE

An interesting question from Valentin Nikotin

Posted on July 21, 2012 by Sayan Malakshinov Posted in Uncategorized 1,690 Page views

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

[sourcecode language=”sql”]
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;
/
[/sourcecode]

[collapse]

Task 2

[sourcecode language=”sql”]
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;
/
[/sourcecode]

[collapse]
Task 3

[sourcecode language=”sql”]
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;
/
[/sourcecode]

[collapse]
Task 4

[sourcecode language=”sql”]
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;
/
[/sourcecode]

[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.

« A lot of “latch free dml allocation latch” in concurrent queries to v$lock
When oracle invalidates result_cache function results without any changes in objects on which depends »
Page views: 1,690
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro Alumni

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • Oracle Telegram Bot
  • Partition Pruning and Global Indexes
  • Interval Search: Part 4. Dynamic Range Segmentation – interval quantization
  • Interval Search Series: Simplified, Advanced, and Custom Solutions
  • Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Popular posts

Recent Comments

  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 4. Dynamic Range Segmentation – interval quantization on Interval Search: Optimizing Date Range Queries – Part 1
  • Oracle SQL | Interval Search Series: Simplified, Advanced, and Custom Solutions on Interval Search: Part 2. Dynamic Range Segmentation – Simplified
  • Oracle SQL | Interval Search: Part 2. Dynamic Range Segmentation – Simplified on Interval Search: Part 3. Dynamic Range Segmentation – Custom Domain Index

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin
©Sayan Malakshinov. Oracle SQL