ANBOB™

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 时也不可以支持使用。

打赏

微信扫一扫，打赏作者吧～