Have you tried creating views in each database where you have a db link with?

While the limit for a VARCHAR2 in PL/SQL is 32767, the limit for a VARCHAR2 in SQL is only 4000. When you store the query in PL/SQL you don't have a problem, but when you run it the SQL engine takes over, and you find the limit for query length is 4000. But creating a view will hopefully help you to make the query much smaller.

SQL> set serveroutput on

declare
v_varchar2 varchar2(32767);
begin
v_varchar2 := RPAD('Itworks', 8000, '!');
dbms_output.put_line(substr(v_varchar2, 1, 254));
end;
/

create table delme ( c1 varchar(4001));

SQL> SQL> 2 3 4 5 6 7 Itworks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!

PL/SQL procedure successfully completed.

SQL> SQL> create table delme ( c1 varchar(4001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype