February PL/SQL Tip of the Month!

From: Cam White <info_at_revealnet.com>
Date: 1998/02/02
Message-ID: <01bd300d$bdc738a0$0d73b1cd_at_Preveal2>#1/1

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. Received on Mon Feb 02 1998 - 00:00:00 CET