Numeric or value error when converting BLOB to CLOB

Question

I have a BLOB column and I need its content in CLOB, so that I can use further string functions. Unfortunately I have only SELECT privilege on the table, so I can't change its structure.

I use DBMS_LOB package and it works fine with relatively small BLOBS. For example with a BLOB 30.000 length is OK, but with 40.000 long BLOB I get

ORA-06502: PL/SQL: numeric or value error%s.

This makes me suspicious that somewhere VARCHAR2 transformation is involved, but I can't figure out how, because I have none VARCHAR2 variables.

My longest BLOB is 63.000 character long.

With this method below I manage to convert 50 out of 53 rows. (The remaining 3 is larger then 30.000 character.)

Any help would be appreciated.

Thanks in advance.

SET serveroutput ON;

DECLARE
  sRuleName [column]%type;
  bRuleBLOB BLOB;
  cRuleBLOB CLOB;

  v_file_size number := dbms_lob.lobmaxsize;
  v_dest_offset number := 1;
  v_src_offset number := 1;
  v_blob_csid number := dbms_lob.default_csid;
  v_lang_context number := dbms_lob.default_lang_ctx;
  v_warning number;

  CURSOR cRules IS
    SELECT [column]
    FROM [table]
    WHERE [column] IN ('x','y','z')
    ORDER BY [column];
BEGIN
  dbms_output.enable(100000);
  OPEN cRules;
  LOOP
  FETCH cRules INTO sRuleName;
  EXIT WHEN cRules%NOTFOUND;
  --dbms_output.put_line(sRuleName);

    SELECT rule
    INTO bRuleBLOB
    FROM [table]
    WHERE [column] = sRuleName;

    dbms_lob.createtemporary(cRuleBLOB, true);

    v_dest_offset  := 1;
    v_src_offset  := 1;

    dbms_lob.converttoclob(cRuleBLOB, bRuleBLOB, v_file_size, v_dest_offset, v_src_offset, v_blob_csid, v_lang_context, v_warning);

    dbms_output.put_line(cRuleBLOB);
    dbms_lob.freetemporary(cRuleBLOB);
  END LOOP;
  CLOSE cRules;
END;

Answer 1

The problem is in

dbms_output.put_line(cRuleBLOB);

Maximum length of single line in output is 32767 bytes.