Huge Strings Using LOB's

The VARCHAR2 type in PL/SQL is limited to 32K in length.  What do you do if you need to work with strings longer than 32K?  One way to do this is using Oracle's Large Objects (LOB's), and, in particular, using the DBMS_LOB package to create a Temporary CLOB (Character Large Object) and concatenate your text into that CLOB to build it up.  Note that regular string concatenation (||) just works on strings and results up to 32K in length in PL/SQL, so, when you are concatenating into a LOB, you will have to use the WRITEAPPEND procedure in the DBMS_LOB package (or other similar procedure in that package) to get around that 32K limit.  If you have a CLOB variable called "l_clob", and you do something like "l_clob := l_clob || l_some_string_to_concatenate;", it will convert the l_clob value on the right side of the equation to a VARCHAR2 before doing the concatenation, possibly giving you invalid results or an error.

Below shows how to create a Temporary CLOB and concatenate strings to it resulting in a string longer than 32K (the result is a 96000 byte string).  You can do a Describe on DBMS_LOB (desc dbms_lob) in sqlplus to see what other functions are available to you to work with LOB's.

set serveroutput on
declare
   l_clob clob := empty_clob;
   l_str varchar2 (32000);
begin
   dbms_lob.createTemporary( l_clob, true );
   dbms_lob.open ( l_clob, dbms_lob.lob_readwrite );
   l_str := rpad('*',32000,'*');
   dbms_lob.writeappend ( l_clob, length (l_str), l_str );
   dbms_lob.writeappend ( l_clob, length (l_str), l_str );
   dbms_lob.writeappend ( l_clob, length (l_str), l_str );
   dbms_output.put_line(dbms_lob.getlength(l_clob));
   dbms_lob.close (l_clob);
   dbms_lob.freeTemporary ( l_clob );
end;
/

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.