Overview
 --------

 This article contains an example of how to perform a piecewise fetch of a 
 LONG column into VARCHAR2 fields using DBMS_SQL.  

 Starting with RDBMS 7.3, the DBMS_SQL package can be used to do a piecewise 
 fetch of a LONG column (which can be up to 2 gigabytes).  The basic example 
 below demonstrates how to use the DEFINE_COLUMN_LONG and COLUMN_VALUE_LONG 
 procedures.  

 Program Notes
 -------------

 The example used in this article was tested on 8.0.x, but it should also work
 for release 7.3.x.

 Example 
 -------

 /* This example shows how to do a piecewise fetch of a LONG column */

 /* Create a table with a long column. */
 CREATE TABLE long_table (col1 INTEGER, col2 LONG);

 /* Insert two rows into the table.  The values being inserted are not
    very long, but the same concept works for longer values. */
 INSERT INTO long_table
   VALUES(1, 'AAAAAAAAAABBBBBBBBBBCCCCCCCCCCDDDDDDDDDD');

 INSERT INTO long_table
   VALUES(1, 'EEEEEEEEEEFFFFFFFFFFGGGGGGGGGGHHHHH');

 COMMIT;

 DECLARE
   cursor1INTEGER;  -- declare a cursor
   ignore      INTEGER;  -- value is meaningless for a SELECT statement
   statement   VARCHAR2(100); -- holds the SQL statement to be executed
   out_val     VARCHAR2(100); -- value of the portion of the column returned
   out_length  INTEGER; -- length of the portion of the column returned
   num_bytes   INTEGER := 10; -- length in bytes of the segment of the column
                              -- value to be selected
   offset      INTEGER; -- the byte position in the LONG column at which
                        -- the SELECT is to start
   num_fetches INTEGER; -- the number of segments returned
   row_count   INTEGER := 0; -- the number of rows selected
 BEGIN
   statement := 'SELECT col2 FROM long_table WHERE col1 = 1';
   cursor1 := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cursor1, statement, DBMS_SQL.NATIVE);
   /* Define the LONG column. */
   DBMS_SQL.DEFINE_COLUMN_LONG(cursor1, 1);
   /* Execute the query. */
   ignore := DBMS_SQL.EXECUTE(cursor1);
   /* Fetch the rows in a loop.  Exit when there are no more rows. */
   LOOP
     IF DBMS_SQL.FETCH_ROWS(cursor1) > 0 THEN
       row_count := row_count + 1;
       offset := 0;
       num_fetches := 1;
       DBMS_OUTPUT.PUT_LINE('Row ' || row_count || ' fetched.');
       /* Get the value of the LONG column piece by piece.  Here a loop
          is used to get the entire column.  The loop exits when there
          is no more data. */
       LOOP
 /* Get the value of a portion of the LONG column. */
         DBMS_SQL.COLUMN_VALUE_LONG(cursor1, 1, num_bytes, offset,
                                    out_val, out_length);
         IF out_length != 0 THEN
           DBMS_OUTPUT.PUT_LINE('Fetch ' || num_fetches || ':  ');
           DBMS_OUTPUT.PUT_LINE('Value Fetched = ' || out_val);
           DBMS_OUTPUT.PUT_LINE('Length of fetched segment= ' || out_length);
           offset := offset + num_bytes;
           num_fetches := num_fetches + 1;
         ELSE EXIT;
         END IF;
         IF out_length < num_bytes THEN EXIT;
         END IF;
       END LOOP;
     ELSE EXIT;
     END IF;
     DBMS_OUTPUT.PUT_LINE('--------------------------------');
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(cursor1);
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(sqlerrm);
       IF dbms_sql.is_open(cursor1) THEN
         DBMS_SQL.CLOSE_CURSOR(cursor1);
       END IF;
 END;