Re: LONG to long in Oracle?

From: Karim Khalfa <karrim_kill_spam__at_club-internet.fr>
Date: 1998/02/12
Message-ID: <34e2cce4.5714246@news.integrityonline2.com>#1/1

On Tue, 03 Feb 1998 08:46:29 +0100, Wassili Kazakos <kazakos_at_fzi.de> wrote:

>thanks for the hint, but the column is defind as LONG. The problem is
>not the maximum capacity of the row but that Oracle doesnt accept quoted
>strings longer than 2000 chars. Maybe this is a parser problem..
>
>The test table is
>create table test
>(
> id varchar2(20) not null,
> long_value LONG null
>);
>
>insert into test (id,long_value) values ('xyz', 'enter 2002 chars
>here....');

The length of a string literal (i.e. stuff between single quotes) and not the length of a varchar2 as someone suggested is limited to 2000 bytes. I don't know how you generate the insert statement, I guess you don't type it manually into SQL*Plus, but rather you generate a script somehow.
In regular SQL*Plus if you concatenate the value you insert into the long you get the same length restriction, but not in PL/SQL. I have just made a test, and the following works : (using your TEST table)

DECLARE
long_vc2 varchar2(32000);
BEGIN
long_vc2:=

' ... 1000 chars ...'||
' ... 1000 chars ...'||
' ... 1000 chars ...'||

etc...
' ... xxx chars ...';
insert into test values('xyz',long_vc2); END; That way I inserted 5997 bytes into the long_value column. This procedure still limits you to the maximum length of a varchar2, which is 32767 bytes. I think I saw somewhere a trick to do it with more data, but I can't remeber where. Might have been DejaNews or maybe Oracle's own Metalink.

Hope this helps,
Karim Received on Thu Feb 12 1998 - 00:00:00 CST