Oracle 使用数据库链接从all_view.text中提取长列值

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@REPORTING_SYSTEM 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;
/
复制