eagle’s home

在oracle 10.2版本中，ORA-24816是一个常见的错误，错误描述如下：

24816, 00000, “Expanded non LONG bind data supplied after actual LONG or LOB column”
// *Cause: A Bind value of length potentially > 4000 bytes follows binding for
// LOB or LONG.
// *Action: Re-order the binds so that the LONG bind or LOB binds are all
// at the end of the bind list.

解决办法很简单，如Action部分所描述的，将LONG和LOB得字段放在绑定列表的后面。

前两天有开发人员问如何解释Cause部分中的“potentially > 4000 bytes”呢？我写了一小段程序来重现这个错误：

首先我建了一个temp表，两个字段x CLOB和Y varchar2(4000)

用Perl写了两段程序:

首先是不正确的bind次序,LOB字段在varchar的前面

cat bad.pl
#!/usr/local/bin/perl

use DBI qw(:sql_types);
use Getopt::Std;
use Data::Dumper;

my $username=shift;
my $passwd= shift;
my $sid=shift;

$primary_dbh=DBI->connect(“dbi:Oracle:$sid”,$username,$passwd) || die “Can not connect to database.”;
$primary_dbh->{LongReadLen}=1024*1024*5;

$insert_sql = qq { insert into temp (
x,
y
)
values (?,?) };

$x=&lpad(“a”,”a”,10000);
$y=&lpad(“a”,”a”,4000);

$insert_sth = $primary_dbh->prepare($insert_sql);

$insert_sth->bind_param( 1, $x, SQL_LONGVARCHAR );
$insert_sth->bind_param( 2, $y);
$insert_sth->execute();

$primary_dbh->disconnect();
print “1 rows has been inserted into table\n”;
sub lpad {
#usage: &lpad(“a”,”a”, 1000000);
return($_[1] x ($_[2] – length($_[0])) . $_[0]);
}

然后是正确的Bind次序，将LOB字段放到了最后

cat good.pl
#!/usr/local/bin/perl

use DBI qw(:sql_types);
use Getopt::Std;
use Data::Dumper;

$username=shift;
$passwd= shift;
$sid=shift;

$primary_dbh=DBI->connect(“dbi:Oracle:$sid”,$username,$passwd) || die “Can not connect to database.”;
$primary_dbh->{LongReadLen}=1024*1024*5;

$insert_sql = qq { insert into temp (
y,
x
)
values (?,?) };

$x=&lpad(“a”,”a”,10000);
$y=&lpad(“a”,”a”,4000);

$insert_sth = $primary_dbh->prepare($insert_sql);

$insert_sth->bind_param( 1, $y);
$insert_sth->bind_param( 2, $x, SQL_LONGVARCHAR );
$insert_sth->execute();

$primary_dbh->disconnect();
print “1 rows has been inserted into table\n”;
sub lpad {
#usage: &lpad(“a”,”a”, 1000000);
return($_[1] x ($_[2] – length($_[0])) . $_[0]);
}

如果环境变量的NLS_LANG和数据库的NLS_CHARACTERSET一致的时候，这两个脚本都是没有问题的:

select * from v$nls_parameters where parameter =’NLS_CHARACTERSET’;
PARAMETER VALUE
—————- —–
NLS_CHARACTERSET UTF8
!env |grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.UTF8

> good.pl username password tnsname
1 rows has been inserted into table
> bad.pl username password tnsname
1 rows has been inserted into table

但是如果我们将NLS_LANG改为AMERICAN_AMERICA.WE8ISO8859P1，bad.pl将报错
bad.pl username password tnsname
DBD::Oracle::st execute failed: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column (DBD ERROR: error possibly near <*> indicator at char 14 in ‘ insert into <*>temp (
x,
y
)
values (:p1,:p2) ‘) [for Statement " insert into temp (
x,
y
)
values (?,?) " with ParamValues: :p1='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...', :p2='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...'] at bad.pl line 27.

good.pl 没有问题
good.pl username password tnsname
1 rows has been inserted into table

当我把$y=&lpad(“a”,”a”,4000);改为$y=&lpad(“a”,”a”,1333);时bad.pl执行成功，改为$y=&lpad(“a”,”a”,1334)后又不行了。

原因就是UTF8为三字节字符集，而WE8ISO8859P1为单字节字符集，因为oracle不知道里面是不是有特殊字符，所有将1334*3=4002 > 4000

Cause部分中的“potentially > 4000 bytes”就是这个意思。client和server端字符集不同只是其中的一种情况，还有其他的原因可能会导致这一情况。

解决办法就如good.pl中所示，将LOB,LONG字段放在最后就可以了。

Eagle Fan oracle 10g