ORA-22922: nonexistent LOB value

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

As mentioned in a previous posting about ORA-22275, a temporary LOB can be turned into a persistent LOB by moving it into a table, however one thing is persistence and a different thing is LOB consistency.

Funny things happen when one gets fancy in the use of SAVEPOINT/ROLLBACK in conjunction with LOBs.

The following PL/SQL block represents a simple scenario of an ill-designed procedure that is rolling back under a certain condition thus invalidating the LOB pointer.

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
  rollback;
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at "SYS.DBMS_LOB", line 560
ORA-06512: at line 19

The procedure can be modified as follows:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
 savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
  rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
end;
/

This time no ORA-22922 will be raised and apparently we managed to transform a temporary LOB locator into a persistent one without inserting any rows, because the insert statement was rolled back.

Interestingly enough on my Oracle XE instance, if i further modify the program as follows, i'm eventually getting ORA-00600:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
 insert into files(document) values(l_clob);
 commit;
end;
/

ORA-00600: internal error code, arguments: [7999], [300], [], [], [], [], [], []
ORA-06512: at line 21

If i insert a COMMIT statement before the final INSERT, we're back to the initial situation:

declare
l_clob   clob;
l_id     number;
simulated_error exception;
begin
savepoint "test";
l_clob := dbms_xmlgen.getxml('select * from user_tables');
insert into files(document) values(l_clob) returning id into l_id;
dbms_lob.freetemporary(l_clob);
select document into l_clob
 from files where id = l_id;

begin
raise simulated_error;
exception
when simulated_error then
 rollback to "test";
when others then raise;
end;
dbms_output.put_line(dbms_lob.getlength(l_clob));
 commit;
insert into files(document) values(l_clob);
commit;
end;
/

ORA-22922: nonexistent LOB value
ORA-06512: at line 22

In conclusion ORA-22922 is a symptom of a program that is incorrectly handling LOB locators across transactions.

For a complete review of weird effects achievable by tampering with LOB locators and partial LOB manipulation procedures, see the linked paragraph in the Oracle Application Developer's Guide for Large Objects - Advanced design considerations chapter.

See message translations for ORA-22922 and search additional resources.