Inserting Large Strings
A question frequently comes up -- how do I insert/update a string greater
then 2,000 bytes in Oracle7.x or 4,000 bytes in Oracle8.x? The answer
is that you need to use bind variables for larger strings instead
of character string constants. Character string constants in SQL
statements are limited to 2,000 bytes in 7.x and 4,000 bytes in 8.x (ANSI
says to make then 255)
One method that works upto 32k is as such:
drop table demo;
create table demo ( x long );
myvar long default '
(lots of stuff here)......
insert into demo values ( myvar );
dbms_output.put_line( length( myvar ) );
The pl/sql variable can be initialized to upto 32k of static text.
It can then be inserted into the table (it is a bind variable, not a constant
at that point).
For example, I just ran the above in SQLPlus and it showed me:
So, that put a 24,726 character string into the table... To go larger
then 32k you need to use ODBC, Pro*C or OCI for LONGS and LONG RAWs or
the dbms_lob package from any language for CLOBs and BLOBs
PL/SQL procedure successfully completed.
All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of
merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or
consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or