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-00942 and ORA-06512 at SYS.XMLTYPE

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

You may get the following puzzling stack of error messages when you attempt to use the XMLTYPE constructor on an empty CLOB.
This error can be easily reproduced in the following way:

select xmltype(empty_clob()) as x
from dual;

ORA-00942: table or view does not exist
ORA-06512: at "SYS.XMLTYPE", line 254
ORA-06512: at line 1

Most likely, in a real life scenario, you initialized a CLOB column with the special value EMPTY_CLOB(), that is standard practice when you need to insert a CLOB value into a table as the result of a conversion from a BLOB type, as shown in this PL/SQL snippet:

...
insert into file_store
      (  name, document, created_by)
values (l_name, empty_clob(), p_user)
returning id, document into l_id, l_clob;

dbms_lob.convertToClob(l_clob, l_blob, DBMS_LOB.LOBMAXSIZE, l_dest_off, l_src_off, l_csid, l_shift, l_warning);
...

Note that you can compare the lob locator with the value EMPTY_CLOB().

...
if l_clob = empty_clob() then
 -- lob locator is empty
...
elsif l_clob is null then

 -- lob locator is atomically null 
...
else

 -- lob locator contains a non empty CLOB
...
end if;
...

in case you need to distinguish between a null CLOB locator and an empty CLOB locator.

Once you are sure that the document is not empty, you can try to apply the XMLType constructor and see if you get any parsing errors.

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