invalid LOB locator specified: ORA-22275

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

Just a simple reminder (I tend to forget things over the centuries) when using procedure DBMS_LOB.CONVERTTOCLOB with persistent lob locators:

1. don't forget to include the FOR UPDATE clause in the SELECT statement fetching the lob locators. 
2. don't forget to initialize the destination lob (in this case the clob column DOC)  with EMPTY_CLOB().

otherwise ORA-22275 may occur (see also another cause of this error).

...
 select doc, bdoc, charset
   into l_clob, l_blob, l_cset
   from file_imports
  where id = p_fileid
    for update;

...
 dbms_lob.converttoclob(
  l_clob,
  l_blob,
  dbms_lob.lobmaxsize,
  l_coff,
  l_boff, 
  l_cset,
  l_context,
  l_warn);
...
 
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 

In the PL/SQL fragment above, column DOC was null, which is a different value from EMPTY_CLOB().
As this column is always null when the record is created, I modified the default value of the column as follows:

alter table file_imports modify doc default empty_clob();

This avoids the annoyance of having to update the row and set DOC to EMPTY_CLOB() before calling DBMS_LOB.CONVERTTOCLOB.


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

ORA-22275: invalid LOB locator specified

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

You may get ORA-22275 in the following situation:

declare
l_clob clob;
begin
l_clob := dbms_xmlgen.getxml('select * from user_tables');
 dbms_lob.freetemporary(l_clob);
insert into files(document) values(l_clob);
end;
/

ORA-22275: invalid LOB locator specified
ORA-06512: at line 6

The problem here is in the premature call of procedure DBMS_LOB.FREETEMPORARY, although the error is raised in the subsequent line.
This particular instance of DBMS_XMLGEN.GETXML is returning a temporary LOB that must be freed after use, therefore DBMS_LOB.FREETEMPORARY must be called after moving the LOB object into its "final" position inside the table.
When you insert a temporary LOB into a table, it becomes persistent.
See also see entry for another occurrence of ORA-22275.

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