Re: Insert into datatype long

Robert J. Sullivan <rjs_at_atl.ema.eds.com> wrote in article <34182747.EE3E43D0_at_atl.ema.eds.com>...
> How does one get a long string (around 10K) into an Oracle table using
> standard SQL?
> The column datatype is "long" which can hold a ton.
> If the long string is passed in the VALUES statement the following error
> appears:
> ORA-01704 String literal too long
> Is there any way to pass it in the VALUES clause?

In standard SQL, a literal string is limited to 2000 bytes. Some character functions, such as lpad(), return varchar2 which is limited to 32K. This is OK if you have a repetitive string. You can use lpad() or rpad() to generate such a varchar2 value that can be inserted into a table.

So you can't do what you want in standard SQL -- but you can do it in PL/SQL. Your literal strings are still limited to 2000 bytes, but now you can concatenate them into a LONG variable, which is then used in the VALUES clause. Using PL/SQL, you can concatenate a very long literal string and stuff it into a long variable, as in:
declare
  long_str long;
begin
  long_str :=
    '12345678901234567890123456789012345678901234567890' ||     '12345678901234567890123456789012345678901234567890' ||     ... /* 50 iterations x 100 substrings = 5000 bytes */     '12345678901234567890123456789012345678901234567890' ;   insert into test_long values (long_str); -- a table with 1 col defined long
end;
/

PL/SQL does an implicit conversion from char (the datatype of a literal string) to long, so that when the right hand side of the assignment is evaluated, the type of the resultant string is long, not char, and can be computed and stuffed into the variable.

• Dan Clamage dclamage AT idcomm DOT com (I've been Spammed right off this news group!)