CREATE TABLE orgnistaion_db1
(
   "DateCompleted"   DATE,
   "CompletedBy"     NUMBER (10),
   "COFSTAId"        NUMBER (10),
   "CONNId"          NUMBER (10),
   "CMUId"           NUMBER (10),
   "comment"         LONG
);

DECLARE
   long_var   LONG;
   var_var    VARCHAR2 (2000);
BEGIN
   FOR i IN (SELECT "comment" FROM orgnistaion_db1@source_db_link)
   LOOP
      var_var := SUBSTR (i."comment", 1, 2000);

      INSERT INTO orgnistaion_db2 ("comment")
           VALUES (var_var);

      COMMIT;
   END LOOP;
END;

BEGIN
  FOR v_rec IN (SELECT * FROM orgnistaion_db1@source_db_link) LOOP
    INSERT
      INTO orgnistaion_db2
      VALUES (
              v_rec."DateCompleted",
              v_rec."CompletedBy",
              v_rec."COFSTAId",
              v_rec."CONNId",
              v_rec."CMUId",
              v_rec."comment"
             );
  END LOOP;
END;
/

SQL> connect scott@sol10
Enter password: *****
Connected.
SQL> create table tbl
  2    as
  3      select  owner,
  4              trigger_name,
  5              to_lob(trigger_body) trigger_body
  6        from  dba_triggers
  7  /

Table created.

SQL> with t as (
  2             select  owner,
  3                     trigger_name,
  4                     max(length(trigger_body)) len
  5               from  tbl
  6               group by owner,
  7                        trigger_name
  8               having max(length(trigger_body)) <= 32767
  9               order by 3 desc
 10            )
 11  select  *
 12    from  t
 13    where rownum <= 3
 14  /

OWNER                          TRIGGER_NAME                          LEN
------------------------------ ------------------------------ ----------
SYSMAN                         BLACKOUT_CHANGE                     16289
SYSMAN                         METRIC_ERRORS_CUR_AND_DUPES         12351
SYSMAN                         CHECK_DUPLICATE_TARGETS              6003

SQL> connect scott@sol11
Enter password: *****
Connected.
SQL> create table tbl(
  2                   owner varchar2(30),
  3                   trigger_name varchar2(30),
  4                   trigger_body clob
  5                  )
  6  /

Table created.

SQL> declare
  2      cursor v_cur
  3        is
  4          select  owner,
  5                  trigger_name,
  6                  trigger_body
  7            from  dba_triggers@sol10
  8            where owner = 'SYSMAN'
  9              and trigger_name in (
 10                                   'BLACKOUT_CHANGE',
 11                                   'METRIC_ERRORS_CUR_AND_DUPES',
 12                                   'CHECK_DUPLICATE_TARGETS'
 13                                  );
 14  begin
 15      for v_rec in v_cur loop
 16        insert
 17          into tbl
 18          values(
 19                 v_rec.owner,
 20                 v_rec.trigger_name,
 21                 v_rec.trigger_body
 22                );
 23      end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

SQL> column owner format a6
SQL> column trigger_body format a40
SQL> select  *
  2    from  tbl
  3  /

OWNER  TRIGGER_NAME                   TRIGGER_BODY
------ ------------------------------ ----------------------------------------
SYSMAN BLACKOUT_CHANGE                DECLARE

                                          latest_availability_rowid ROWID;
                                          latest_availability_status NUM

SYSMAN CHECK_DUPLICATE_TARGETS        DECLARE
                                        l_cnt_url    NUMBER := 0;
                                        l_avail_cnt  NUMBER := 0;
                                        l_old_agent_na

SYSMAN METRIC_ERRORS_CUR_AND_DUPES    DECLARE

OWNER  TRIGGER_NAME                   TRIGGER_BODY
------ ------------------------------ ----------------------------------------

                                          current_availability_rowid ROWID;
                                          current_availability_status N


SQL> 

 select * from table_with_long_column@source_db_link where "column"=9159  

 select * from table_with_long_column@source_db_link 

SQL> select  *
  2    from  v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> select  *
  2    from  v$version@link1
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for HPUX: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> create table test_long(body long)
  2  /

Table created.

SQL> declare
  2      cursor v_cur
  3        is
  4          select  body
  5            from  hyper_user.hbr_allocprocs@link1;
  6  begin
  7      for v_rec in v_cur loop
  8        insert
  9          into test_long
 10          values(
 11                 v_rec.body
 12                );
 13      end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> -- and to check how long LONGs are
SQL> create table tbl
  2    as
  3      select  to_lob(body) body
  4        from  test_long
  5  /

Table created.

SQL> select  length(body)
  2    from  tbl
  3  /

LENGTH(BODY)
------------
        1929
       12772
        3855
        3811
        4630
        6088
        4373
       13652
       13917
       15159
        5758

LENGTH(BODY)
------------
       13748
        5305
       14080
       14081
        1988
       13374
        5876
        4793
       19384
        4266
        6180

22 rows selected.

SQL>