问题描述
Creates a variable of type VARCHAR2 with length of up to n bytes or n characters. The maximum that n can be is 4000 bytes,
pl/sql VARCHAR2 Datatype
You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:
VARCHAR2(maximum_size [CHAR | BYTE])
You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.
Although PL/SQL character variables can be relatively long, you cannot insert VARCHAR2 values longer than 4000 bytes into a VARCHAR2 database column.
专家解答
You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable.
anbob@ANBOB> create table testchr(id int,context varchar2(4000));
Table created.
anbob@ANBOB> declare
2 v_biggerchr varchar2(32767):=rpad('a',4001,'o');
3 begin
4 insert into testchr values(1,substr(v_biggerchr,1,200));
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
anbob@ANBOB> declare
2 v_biggerchr varchar2(32767):=rpad('a',4001,'o');
3 begin
4 execute immediate 'insert into testchr values(1,:chr)' using substr(v_biggerchr,1,200);
5 end;
6 /
PL/SQL procedure successfully completed.
anbob@ANBOB> declare
2 v_biggerchr varchar2(32767):=rpad('a',4001,'o');
3 begin
4 v_biggerchr:= substr(v_biggerchr,1,200);
5 insert into testchr values(1,v_biggerchr);
6 end;
7 /
PL/SQL procedure successfully completed.note:varchar2(N) 最大支持4000byte长度，而pl/sql varchar2(n)确可以支持到32767长度，虽然plsql varchar2中支持大于4000byte,但是在insert 时也不可以支持使用。
