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 );
declare
    myvar long default '
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
(lots of stuff here)......
';
begin
   insert into demo values ( myvar );
   dbms_output.put_line( length( myvar ) );
end;

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:

Table dropped.
Table created.
24726
PL/SQL procedure successfully completed.

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
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 these materials.