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