|
|
Cooperative Extension Service |
|
 |
||
|
|
||
|
Agricultural
Experiment Station |
||
 |
|
|
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;
/
This Page was Last Updated on 06/04/07
You Are Visitor Number |
|
|
© 2006 |
|
|
University of Arkansas • Division of Agriculture |
Mission
•
Disclaimer
•
EEO
•
|
