CREATE TABLE san_ora03101_test  
( resource_name VARCHAR2 (100), 
  view_owner    VARCHAR2(100), 
  view_name   VARCHAR2(100), 
  text      CLOB 
)

DECLARE 
 
    s_sql      CLOB:= q'[SELECT * 
                          FROM ( 
                                SELECT v.view_name resource_name 
                                     , v.owner        view_owner 
                                     , v.view_name 
                                     , v.text text 
                                  FROM all_views@<DB_LINK> v)]';  
    cur        BINARY_INTEGER; 
    s_res_name VARCHAR2(100); 
    s_owner    VARCHAR2(100); 
    s_vname    VARCHAR2(100); 
    c_text     CLOB; 
    n_res      NUMBER; 
BEGIN       
 
        cur:=DBMS_SQL.OPEN_CURSOR; 
        DBMS_SQL.PARSE(cur, s_sql, DBMS_SQL.NATIVE); 
        DBMS_SQL.DEFINE_COLUMN(cur, 1, s_res_name, 100); 
        DBMS_SQL.DEFINE_COLUMN(cur, 2, s_owner, 100); 
        DBMS_SQL.DEFINE_COLUMN(cur, 3, s_vname, 100); 
        DBMS_SQL.DEFINE_COLUMN_LONG(cur, 4); 
 
        n_res:=DBMS_SQL.EXECUTE(cur); 
        WHILE DBMS_SQL.FETCH_ROWS(cur) > 0 LOOP 
            DBMS_SQL.COLUMN_VALUE(cur, 1, s_res_name); 
            DBMS_SQL.COLUMN_VALUE(cur, 2, s_owner); 
            DBMS_SQL.COLUMN_VALUE(cur, 3, s_vname); 
            DECLARE 
                c_tmp   CLOB; 
                n_pos   INTEGER:=0; 
                s_tmp   VARCHAR2(32767); 
                n_tpos  INTEGER; 
                n_piece_len NUMBER:= 32767; 
            BEGIN 
                LOOP 
                    dbms_output.put_line(' n_pos: '||n_pos||',s_tmp: '||s_tmp||',n_tpos: '||n_tpos||',cur: '||cur||', s_res_name: '||s_res_name||',s_owner: '||s_owner||',s_vname: '||s_vname); 
                     
          DBMS_SQL.COLUMN_VALUE_LONG(cur, 4, n_piece_len, n_pos, s_tmp, n_tpos); 
                         
                    c_tmp:=c_tmp||s_tmp; 
                    n_pos:=n_pos+n_piece_len; 
                    EXIT WHEN n_tpos < n_piece_len; 
                END LOOP; 
                c_text:=c_tmp; 
                 
            EXCEPTION 
                WHEN OTHERS THEN 
                  dbms_output.put_line(' ERR_STACK: '||dbms_utility.format_error_stack||', SQL_TRACE: '||dbms_utility.format_error_backtrace); 
                RAISE; 
            END; 
             
        -- INSERT RECORDS 
        INSERT 
          INTO san_ora03101_test 
             ( 
               resource_name 
             , view_owner 
             , view_name 
             , text 
             ) 
        VALUES 
             ( 
               s_res_name 
             , s_owner 
             , s_vname 
             , c_text 
             ); 
 
        END LOOP; 
        DBMS_SQL.CLOSE_CURSOR(cur); 
        COMMIT; 
EXCEPTION 
    WHEN OTHERS THEN 
        IF DBMS_SQL.IS_OPEN(cur) THEN 
            DBMS_SQL.CLOSE_CURSOR(cur); 
        END IF; 
        RAISE; 
END;