DBMS_SQL provides a separate procedure, COLUMN_VALUE_LONG, to allow you to retrieve LONG values from a dynamic query.

Syntax

PROCEDURE DBMS_SQL.COLUMN_VALUE_LONG
    (c IN INTEGER
    ,position IN INTEGER
    ,length IN INTEGER
    ,offset IN INTEGER
    ,value OUT VARCHAR2
    ,value_length OUT INTEGER);

Parameter Description
c Pointer to the cursor.
position Relative position of the column in the select list.
length The length in bytes of the portion of the LONG value to be retrieved.
offset The byte position in the LONG column at which the retrieval is to start.
value The variable that will receive part or all of the LONG column value.
value_length The actual length of the retrieved value.

The COLUMN_VALUE_LONG procedure offers just about the only way to obtain a LONG value from the database and move it into PL/SQL data structures in your program. You cannot rely on a static SELECT to do this. Instead, use DBMS_SQL and both the DEFINE_COLUMN_LONG and COLUMN_VALUE_LONG procedures.

Example

The following example demonstrates the technique, and, in the process, offers a generic procedure called dump_long that you can use to dump the contents of a long column in your table into a local PL/SQL table. The dump_long procedure accepts a table name, column name, and optional WHERE clause. It returns a PL/SQL table with the LONG value broken up into 256-byte chunks.

CREATE OR REPLACE PROCEDURE dump_long (
   tab IN VARCHAR2,
   col IN VARCHAR2,
   whr IN VARCHAR2 := NULL,
   pieces IN OUT DBMS_SQL.VARCHAR2S)
IS
   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   fdbk PLS_INTEGER;

   TYPE long_rectype IS RECORD (
      piece_len PLS_INTEGER,
      pos_in_long PLS_INTEGER,
      one_piece VARCHAR2(256),
      one_piece_len PLS_INTEGER
      );
   rec long_rectype;

  BEGIN
 /* Construct the query, sticking in a non-restricting filter
       if whr is NULL */
   DBMS_SQL.PARSE (
      cur,
      'SELECT ' || col ||
      '  FROM ' || tab ||
      ' WHERE ' || NVL (whr, '1 = 1'),
      DBMS_SQL.NATIVE);

   /* Define the long column and then execute and fetch... */
   DBMS_SQL.DEFINE_COLUMN_LONG (cur, 1);
   fdbk := DBMS_SQL.EXECUTE (cur);
   fdbk := DBMS_SQL.FETCH_ROWS (cur);

   /* If a row was fetched, loop through the long value until
   || all pieces are retrieved.
   */
   IF fdbk > 0
   THEN
      rec.piece_len := 256;
      rec.pos_in_long := 0;
      LOOP
         DBMS_SQL.COLUMN_VALUE_LONG (
            cur,
            1,
            rec.piece_len,
            rec.pos_in_long,
            rec.one_piece,
            rec.one_piece_len);
         EXIT WHEN rec.one_piece_len = 0;

         /* Always put the new piece in the next available row */
         pieces (NVL (pieces.LAST, 0) + 1) := rec.one_piece;
         rec.pos_in_long := rec.pos_in_long + rec.one_piece_len;
      END LOOP;
   END IF;
   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

To test this procedure, we created a table with a LONG column as follows:

DROP TABLE nextbook;
CREATE TABLE nextbook
   (title VARCHAR2(100), text LONG);
INSERT INTO nextbook VALUES
   ('Oracle PL/SQL Quick Reference',
    RPAD ('INSTR ', 256, 'blah1 ') ||
    RPAD ('SUBSTR ', 256, 'blah2 ') ||
    RPAD ('TO_DATE ', 256, 'blah3 ') ||
    RPAD ('TO_CHAR ', 256, 'blah4 ') ||
    RPAD ('LOOP ', 256, 'blah5 ') ||
    RPAD ('IF ', 256, 'blah6 ') ||
    RPAD ('CURSOR ', 256, 'blah7 ')
    );

We then put together this short test script. It extracts the single value from the table. (a NULL WHERE clause is passed, so it simply returns the first—and only—row fetched.) It then uses a numeric FOR loop to scan through the returned table to display the results.

  DECLARE
   mytab DBMS_SQL.VARCHAR2S;
BEGIN
   dump_long ('nextbook', 'text', NULL, mytab);
   FOR longind IN 1 .. mytab.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE  (SUBSTR (mytab(longind), 1, 60));
   END LOOP;
END;
/

Here is the output displayed in the SQL*Plus window:

INSTR blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1 blah1
SUBSTR blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2 blah2
TO_DATE blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah3 blah
TO_CHAR blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah4 blah
LOOP blah5 blah5 blah5 blah5 blah5 blah5 blah5 blah5 blah5 b
IF blah6 blah6 blah6 blah6 blah6 blah6 blah6 blah6 blah6 bla
CURSOR blah7 blah7 blah7 blah7 blah7 blah7 blah7 blah7 blah7