Re: Getting data in to LONGs

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/27
Message-ID: <347cd875.25010383@inet16>#1/1

On Tue, 25 Nov 1997 19:05:50 +0000, Andy McLeod <andy_at_aligrafix.co.uk> wrote:

>I'm sure this is a dumb question, but how do I get
>large quantities of text into LONG fields? I can't
>use
>
>INSERT VALUES('<loads of text>')
>
>as Oracle barfs if the quoted string is over 2000
>characters.
>
>Suggestions welcome.
>
>thx/andy

You can do this upto 32k 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;
/
select count(*) from abc;

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  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Nov 27 1997 - 00:00:00 CST