From: "Cam White" <info@revealnet.com>
Subject: February PL/SQL Tip of the Month!
Date: 1998/02/02
Message-ID: <01bd300d$bdc738a0$0d73b1cd@Preveal2>#1/1
Organization: CAIS Internet
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.tools



From the "PL/SQL Pipeline" - a free internet community for Oracle
developers. http://www.revealnet.com/plsql-pipeline/index.htm

February's Tip of the Month

Working with Really Long LONGs in PL/SQL Programs 

It can be a real challenge working real big strings in PL/SQL – especially
when you try to grab them out of the database and move them to local PL/SQL
variables. Regular VARCHAR2 columns in a table can hold up to 2000
characters (4000 in Oracle8). Regular VARCHAR2 variables in PL/SQL can hold
up to 32K characters. But LONG columns in a table can hold up to 2
gigabytes of data. So what do you do when you have, say, a 1 gigabyte chunk
of data in PL/SQL and you want to manipulate it in a PL/SQL program? 

Oracle offers two new additions (as of Oracle7.3 and above) to the DBMS_SQL
built-in package to make it easier for you to do this: 

DBMS_SQL.DEFINE_COLUMN_LONG – Defines a column as holding a LONG.
DBMS_SQL.COLUMN_VALUE_LONG – Extracts up to 256 bytes of the LONG with each
call. 

The Archive entry for the PL/SQL Pipeline in February will offer a generic
program to dump the value of a LONG column of any database table into a
PL/SQL table. For now, though, here is an example of the kind of code you
would write to grab the value from the LONG text column of my nextbook
table: 


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 ') 
    );
/    

DECLARE
   /* You will need Oracle7.3 or above */

   cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
   fdbk PLS_INTEGER;

   pos_in_long PLS_INTEGER;
   one_piece VARCHAR2(256);
   one_piece_len PLS_INTEGER;

   /* Assume less than 32K, just concatenate into
      a big string. In the "real world", you would
      write into a PL/SQL table, as you will see in
      the February 1998 Archive. */

   mylong VARCHAR2(32767);
BEGIN
   DBMS_SQL.PARSE (
      cur,
      'SELECT text FROM nextbook WHERE 
          INSTR (title, ''PL/SQL'') > 0',
      DBMS_SQL.NATIVE);

   DBMS_SQL.DEFINE_COLUMN_LONG (cur, 1);

   fdbk := DBMS_SQL.EXECUTE_AND_FETCH (cur);

   IF fdbk > 0
   THEN
      pos_in_long := 0;

      LOOP
         /* Call the new built-in inside a loop, so that
            you can grab all of the values. */

         DBMS_SQL.COLUMN_VALUE_LONG (
            cur,
            1,
            256,
            pos_in_long,
            one_piece,
            one_piece_len);

         EXIT WHEN one_piece_len = 0;

         mylong := mylong || one_piece;

         /* Move to the next chunk of the LONG value. */
         pos_in_long := pos_in_long + one_piece_len;
      END LOOP;
   END IF;

   DBMS_SQL.CLOSE_CURSOR (cur);

   /* Display the long value using PL/Vision substitute for 
      DBMS_OUTPUT so that we don't get a value error. */
   p.l (mylong);
END;
/

Visit the PL/SQL Pipeline for free white papers, utilities and technical
discussions with Oracle developers around the world. The PL/SQL Pipeline is
hosted by PL/SQL author and instructor Steven Feuerstein, and sponsored by
RevealNet.

Best wishes,

Cam White
RevealNet, Inc.