Re: Fun With VARCHAR2

The varchar2 in your PL/SQL is not the same than the varchar2 declared in a database.

The first is used for declaring text from one character to a long string (2 Go ?). It represents a memory adresse during the time the program PL/SQL runs.

But the second is a datatype which has a limit of 4000 character (Oracle V8).

When the compiler finds you try to insert 4001 characters into a column, it search normally for a long column because the varchar2(4001) is overloaded as a long.

Yang

Martin Haltmayer wrote in message <38EE6349.D42AB20D_at_0800-einwahl.de>...
>Oracle 8.1.5, NT 4.0 SP 6
>
>I get two different error messages depending on the length of a varchar2.
 Both
>are too long, but they yield different error messages:
>
>SQL>
>SQL> create table test (n varchar2 (2000));
>
>Table created.
>
> real: 351
>SQL>
>SQL> declare
> 2 s varchar2 (4000):= lpad ('x', 4000, 'x');
> 3 begin
> 4 insert into test (n) values (s);
> 5 end;
> 6 /
>declare
>*
>ERROR at line 1:
>ORA-01401: inserted value too large for column
>ORA-06512: at line 4
>
>
> real: 310
>SQL>
>SQL> declare
> 2 s varchar2 (4001):= lpad ('x', 4001, 'x');
> 3 begin
> 4 insert into test (n) values (s);
> 5 end;
> 6 /
>declare
>*
>ERROR at line 1:
>ORA-01461: can bind a LONG value only for insert into a LONG column
>ORA-06512: at line 4
>
>
> real: 0
>SQL>
>SQL> drop table test;
>
>Table dropped.
>
>Funny, isn't it? You would never expect an error message about a long
 column
>unless you use longs, right?
>
>Martin
Received on Sat Apr 08 2000 - 00:00:00 CDT