1rem ----------------------------------------------------------------------- 2rem URL: http://www.orafaq.com/scripts/plsql/readlong.txt 3rem Filename: readlong.sql 4rem Purpose: Fetch Long column values piece-wise from PL/SQL 5rem Date: 12-Jan-1999 6rem Author: Frank Naude (frank@ibi.co.za) 7rem ----------------------------------------------------------------------- 8 9set serveroutput on 10 11-- Create test table 12drop table longtable; 13create table longtable (longcol long) tablespace TOOLS; 14insert into longtable values ( rpad('x', 257, 'QWERTY') ); 15 16DECLARE 17 cur1 PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;; 18 rc NUMBER; 19 long_piece VARCHAR2(256); 20 piece_len INTEGER := 0; 21 long_tab DBMS_SQL.VARCHAR2S; 22 long_len INTEGER := 0; 23BEGIN 24 DBMS_SQL.PARSE(cur1, 'select longcol from longtable', DBMS_SQL.NATIVE); 25 DBMS_SQL.DEFINE_COLUMN_LONG(cur1, 1); 26 rc := DBMS_SQL.EXECUTE(cur1); 27 rc := DBMS_SQL.FETCH_ROWS(cur1); -- Get one row 28 29 -- Loop until all pieces of the long column are processed 30 LOOP 31 DBMS_SQL.COLUMN_VALUE_LONG(cur1, 1, 256, long_len, long_piece, piece_len); 32 EXIT WHEN piece_len = 0; 33 DBMS_OUTPUT.PUT_LINE('Long piece len='|| piece_len); 34 35 long_tab( NVL(long_tab.LAST, 0)+1 ) := long_piece; -- Add piece to table 36 long_len := long_len + piece_len; 37 END LOOP; 38 DBMS_SQL.CLOSE_CURSOR(cur1); 39 DBMS_OUTPUT.PUT_LINE('Total long col fetched, len='|| long_len); 40END; 41/ 42