0

ORA-01461: can bind a LONG value only for insert into a LONG column解决

张维照 2019-05-31
100

问题描述

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

「喜欢文章,快来给作者赞赏墨值吧」
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

关注
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部