Oracle Database Discussions

Hi All,

I have a function which takes BLOB and converts it into CLOB using DBMS_LOB.CONVERTTOCLOB.

I am calling this function from stored procedure.

Function is similar to function given in Convert Blob to Clob

SQL> create or replace function BlobToClob( b blob ) return clob is
  2          c               clob;
  3          srcOffset       number := 1;
  4          dstOffset       number := 1;
  5          warning         number;
  6          langContext     number := DBMS_LOB.default_lang_ctx;
  7  begin
  8          DBMS_LOB.CreateTemporary( c, true );
  9 
10          DBMS_LOB.ConvertToClob(
11                  dest_lob => c,
12                  src_blob => b,
13                  amount => DBMS_LOB.GetLength(b),
14                  dest_offset => dstOffset,
15                  src_offset => srcOffset,
16                  blob_csid => DBMS_LOB.default_csid,
17                  lang_context => langContext,
18                  warning => warning
19          );
20 
21          return( c );
22  end;
23  /

While executing procedure it gives below error for 1 record.

ORA-22994: source offset is beyond the end of the source LOB

I found that record is updated with EMPTY_BLOB() and it's DBMS_LOB.getlength result was returning me "0".

Then i just update this record with NULL and executed procedure again, procedure executed successfully without any error.

Now i want to understand why this issue occurs, what could be the reason.

Thanks,

Mohsin Javed