Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> February PL/SQL Tip of the Month!
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;
/* 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 CST