Re: LONG data field

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 May 1999 23:24:23 GMT
Message-ID: <373bd2f2.34586622@192.86.155.100>

A copy of this was sent to "Jim Sims" <sims5311_at_students.sou.edu> (if that email address didn't require changing) On Wed, 5 May 1999 16:20:39 -0700, you wrote:

>The only fix I've found is sql*loader.
>
>>How do I insert a value larger than 2000 bytes into a long data field?
>
>

what language are you using to get stuff in? you need to use bind variables for larger strings instead of character string constants. In Pro*C or OCI for example, you can stuff as much into a long as you want.

One method in SQL plus that works upto 32k and demonstrates 'bind variables' 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 it 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...

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed May 05 1999 - 18:24:23 CDT