Re: using the long datatype in Oracle 7.2

But you're limited to a max string size of 32K. To insert longer LONGs, you need to use Pro*C or similar product.

I sent an email to Keith Majkut, Sr. SW Engineer at Oracle, asking him to pass on to the PL/SQL development team a request for a way to insert 2GB of LONG data, as there is a way to fetch LONGs in PL/SQL, using DBMS_SQL.column_value_long. I didn't get a reply, however. Maybe when 8.1 rolls along...

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Thomas Kyte wrote in message <366ff844.9899564_at_192.86.155.100>...
>A copy of this was sent to Nancy Capitanio <nlcapit_at_mailbox.ucdavis.edu>
>(if that email address didn't require changing)
>On Wed, 02 Dec 1998 15:15:03 -0800, you wrote:
>
>>Hello,
>>
>>I am trying to insert more than 2000 characters into a column defined as
>>long.
>>
>>Create table test (
>> field1 long
>>)
>>
>>When I do, I get the following message:
>>
>>01704, 00000, "string literal too long"
>>// *Cause: The string literal is longer than 2000 characters.
>>// *Action: Use a string literal of at most 2000 characters.
>>// Longer values may only be entered using bind variables.
>>
>>
>>The only reference I could find to "bind variables" was regarding PL/SQL
>>variables, so I tried creating a procedure that concatenates two 2000
>>character variables and then inserts them:
>>
>>declare
>> var11 varchar2(2000);
>> var12 varchar2(2000);
>>begin
>> var11 := '<a string of 1,999 ones>';
>> var12 := '<a string of 1,999 ones>';
>> insert into test (field1) values (var11||var12);
>> commit;
>>end;
>>
>>This causes the error:
>>
>>01489, 00000, "result of string concatenation is too long"
>>// *Cause: String concatenation result is more than the maximum size.
>>// *Action: Make sure that the result is less than the maximum size.
>>
>>Can someone please tell me how one inserts > 2000 characters in a long
>>column?
>>
>>Thanks very much,
>>
>>Nancy Capitanio
>>Programmer Analsyt
>>UC Davis
> you need to use bind variables for
>larger strings instead of character string constants.
>
>One method in SQL plus that works upto 32k is as such:
>
>drop table demo;
>create table demo ( x long );
>
>declare
> myvar long default '
>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xx
>(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...
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Herndon VA
>
>--
>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 Wed Dec 02 1998 - 17:23:40 CST