Re: Inserting long data

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/04
Message-ID: <346038c5.3820093@newshost>#1/1

On 04 Nov 1997 09:35:02 +0100, Marc.Fleischeuers_at_kub.no-spam.nl wrote:

>"Joseph S. Testa" <jtesta_at_scioto.net> writes:
>
>> David P. Baker wrote:
>>
>> > Hi. We're running into a problem that must have been solved already: we're
>> > trying to insert data into a LONG column, and we're getting "ORA-01704: string
>> > literal too long". According to the book, a string literal can have no more
>> > than 2,000 characters.
>> >
>> > Is there any way to get around this, except by splitting up the string and
>> > concatenating the (< 2,000 chars) pieces?
>>
>> How about up to 32K using pl/sql and a pl/sql variable that is defined as
>> varchar2(32760). the only problem is you still cant do the > 2K input string from
>> keyboard.
>
>There's still an 'insert' that you have to perform, and this insert is
>subjected to the 2000-char limit. Concatenation cannot be done with an
>insert; updating a LONG through concentenation is out because
>concatenation of LONGs is out. I've been banging my head on this for
>a while; if there's anything I missed I'd certainly love to
>hear. (incidentally: cutting up the LONG column in *fetching* is
>possible in pl/sql with Oracle 7.3)
>
>The bottom line AFAIK is that it cannot be done in Oracle 7 with
>(pl/)sql. Your options are: import/export, Java, Pro*[language of
>choice], OCI, or Oracle 8.
>
>HTH -
>Marc

The bottom line is that you CAN do this in pl/sql AS LONG AS you use a bind variable and not a character string constant. Try this:

drop table abc;
create table abc ( xyz long );  

set serveroutput on  

declare

    l_var long default '

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.... 48 lines deleted of xxxxxxxxxx .......

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

begin

    dbms_output.put_line( length( l_var ) );     insert into abc values ( l_var );
end;
/

putting back in the 48 lines of xxxxxxxxxxxxxxxxxxxxxxxxxxxxx's.  You'll see

output like:

Table dropped.
Table created.  

3850  

PL/SQL procedure successfully completed.    

  COUNT(*)

---

         1

The character string length limit is just that -- a limit on the length of a character string literal in a SQL statment. It is easily gotten around by using bind variables. It is 2,000 bytes in 7.x and 4,000 bytes in 8.x  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.  

I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Tue Nov 04 1997 - 00:00:00 CST