Just a simple reminder (I tend to forget things over the centuries) when using procedure DBMS_LOB.CONVERTTOCLOB with persistent lob locators:
- don't forget to include the FOR UPDATE clause in the SELECT statement fetching the lob locators.
- don't forget to initialize the destination lob (in this case the clob column DOC) with EMPTY_CLOB().
... 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.