We have a chunking function we borrowed to bring back CLOBS over a DB Link in Oracle:

create or replace function fn_dblink_clob(
    p_dblink    in varchar2
  , v_remote_table in varchar2
  , p_clob_col  in varchar2
  , p_rid       in urowid
)
return clob is
    /**  A function to fetch a CLOB column over a dblink  **/
    /** 25-Aug-17  **/
    /**  select dblink_clob('some_dblink', 'some_clob_column', rowid)
           from some_table@some_dblink;
         Note: Does not work on tables that have a virtual column (eg. xmltype).
    **/

    c_chunk_size    constant pls_integer := 4000;
    v_chunk         varchar2(5000);
    v_clob          clob;
    v_pos           pls_integer := 1;
begin
    dbms_lob.createtemporary(v_clob, true, dbms_lob.call);
    loop
        execute immediate 
            'select dbms_lob.substr@' ||p_dblink|| '(' ||p_clob_col|| ', ' ||c_chunk_size
         || ', ' ||v_pos|| ') from ' ||v_remote_table|| '@' ||p_dblink|| ' where rowid = :rid '
        into v_chunk using p_rid;
        begin dbms_lob.append(v_clob, v_chunk);
        exception when others then
            if sqlcode = -6502 then exit; else raise; end if;
        end;
        if length(v_chunk) < c_chunk_size then exit; end if;
        v_pos := v_pos + c_chunk_size;
    end loop;
    return v_clob;
end fn_dblink_clob;

It has worked well, but we are no seeing it throw this error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
ORA-02063: preceding 2 lines from SAPP_TRANSCT_DB
ORA-06512: at "PV_COMPLIANCE.FN_DBLINK_CLOB", line 22
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
CLOBS over DBLINKs are my arch nemesis. I don't know which record is causing it, and dont want to iterate over 5 million records in a cursor to see where it breaks.

Any ideas appreciated.

oracleclob

db1
----
SQL> create table t ( c clob );

Table created.

SQL> insert into t
  2  values (rpad('x',120000,'x'));

1 row created.

SQL> commit;

Commit complete.

db2
----
SQL> create database link xxx connect to scott identified by tiger using '192.168.1.182:1521/pdb1';

Database link created.

SQL> create table t ( c1 clob);

Table created.

SQL> insert into t
  2  select * from t@xxx;

1 row created.