Thursday, March 28, 2013

invalid LOB locator specified: ORA-22275

Always check out the original article at 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;

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.

1 comment:

Anonymous said...

I used your solution.
but it did not work :(.

Error report:
ORA-22275: invalid LOB locator specified
ORA-06512: at line 29
22275. 00000 - "invalid LOB locator specified"
*Cause: There are several causes
initialized; (2) the locator is for a BFILE and the routine
expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
(4) trying to update the LOB in a trigger body -- LOBs in
trigger bodies are read only; (5) the locator is for a
BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
(6) the locator is for a CLOB/NCLOB and the routine expects
a BFILE/BLOB locator;
*Action: For (1), initialize the LOB locator by selecting into the locator
variable or by setting the LOB locator to empty. For (2),(3),
(5) and (6)pass the correct type of locator into the routine.
For (4), remove the trigger body code that updates the LOB value.

I presented the problem in other forums,
but did not have an answer I answer.

Do you have any solutions?

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles