This seems to be bug 1400539.
See MetaLink document 241358.1 for an explanation and workarounds.
HTH
Jared
On Fri, 2005-07-29 at 01:00, Teemu Kivioja wrote:
> Hi,
>
> Even though character encoding issues have been discussed also here, I still
> cannot figure out what is going on in my system.
>
> I try to load data from XML documents and insert into an Oracle database. A
> simple test program (given below together with the trace) that tries to read
> and insert a string of 2075 a:s to a varchar2(4000) field fails:
>
> $ /opt/perl-5.8.7/bin/perl utf_test4000.pl
> UTF8 on, ASCII, 2075 characters 2075 bytes
> DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for
> insert into a LONG column (DBD ERROR: error possibly near <*> indicator at
> char 19 in ...
>
> XML-parsing causes conversion to UTF8 but as the nice data_string_desc
> function
> shows, the string should still be only 2075 bytes long. Why does it look
> like double space is needed compared to the actual length?
>
> I have observed that
> - the problem disappears if I truncate the to less than 2000 chars:
> UTF8 on, ASCII, 1953 characters 1953 bytes
>
> - the problem disappears if I make the field varchar2(10) and store 9 a:s:
> UTF8 on, ASCII, 9 characters 9 bytes
>
> - the problem disappears if I read from stdin i.e. get the UTF8 flag off:
> UTF8 off, ASCII, 2073 characters 2073 bytes
>
> (- however the problem does appear when reading from stdin if I use Perl
> 5.8.0 and an old DBI instead of 5.8.7 and a new one. I first had this problem
> with a plain text file, installed 5.8.7, got rid of it but now l still have
> the same problem when reading from XML-files.)
>
> - setting NLS_LANG to various values does seem to affect the behavior.
>
> Thanks for your attention
> Teemu Kivioja
>
>
> ====== system =======
> Linux, 2.4.21-32.0.1.ELsmp
> Oracle 10g, version 10.1.0.3.0
> Perl 5.8.7 built for i686-linux
> DBI-1.48
> DBD-Oracle-1.16
> locale: LANG=en_US.UTF-8
>
> ====== code =========
> use DBI qw(:utils);
> use XML::Parser;
>
> my $str;
>
> my $xp = new XML::Parser(Handlers => { Start => \&start_handler,
> Char => \&char_handler,
> End => \&end_handler });
> $xp->parsefile('test4000.xml');
>
> my $user = "";
> my $passwd = "";
>
> my $db = "";
> my $dbname = "";
> my $server = "";
>
> DBI->trace(2);
>
> my $dbh = DBI->connect("dbi:Oracle:host=$server;sid=$dbname", $user, $passwd,
> {
> RaiseError => 1, AutoCommit => 0
> });
>
> my $sth = $dbh->prepare( q{
> INSERT INTO test_long_str (id, str) VALUES (?, ?)
> });
>
> my $id = 3;
>
> my $description = data_string_desc($str);
> print("$description\n");
>
> $sth->execute($id, $str);
>
> $dbh->commit;
> $dbh->disconnect;
>
>
>
> #------- functions --------------------#
>
> sub start_handler {
> my($xp) = @_;
> $xp->{cdata_buffer} = '';
> }
>
> sub char_handler {
> my($xp, $data) = @_;
> $xp->{cdata_buffer} .= $data;
> }
>
> sub end_handler {
> my($xp) = @_;
> $str = $xp->{cdata_buffer};
> }
>
> ===== trace ===========
> $ /opt/perl-5.8.7/bin/perl utf_test4000.pl
> DBI 1.48-nothread default trace level set to 0x0/2 (pid 32686)
> -> DBI->connect(dbi:Oracle:host=sboracle1.ad.vtt.fi;sid=BfxDB, belkit,
> ****, HASH(0x989b180))
> -> DBI->install_driver(Oracle) for linux perl=5.008007 pid=32686
> ruid=500 euid=500
> install_driver: DBD::Oracle version 1.16 loaded from
> /opt/perl-5.8.7/lib/site_perl/5.8.7/i686-linux/DBD/Oracle.pm
> -> STORE in DBD::_::common for DBD::Oracle::dr
> (DBI::dr=HASH(0x9969f68)~0x9a10e54 'ShowErrorStatement' 1)
> <- STORE= 1 at Oracle.pm line 61
> <- install_driver= DBI::dr=HASH(0x9969f68)
> -> connect for DBD::Oracle::dr (DBI::dr=HASH(0x9969f68)~0x9a10e54
> 'host=sboracle1.ad.vtt.fi;sid=BfxDB' 'belkit' **** HASH(0x98fa3c4))
> connect using
> '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(HOST=sboracle1.ad.vtt.fi)(PROTOCOL=tcp
> )(PORT=1526))(ADDRESS=(HOST=sboracle1.ad.vtt.fi)(PROTOCOL=tcp)(PORT=1521)))(
> CONNECT_DATA=(SID=BfxDB)))' <- connect= DBI::db=HASH(0x9a73550) at DBI.pm
> line 598
> -> STORE for DBD::Oracle::db (DBI::db=HASH(0x98fa43c)~INNER 'RaiseError'
> 1)
> <- STORE= 1 at DBI.pm line 645
> -> STORE for DBD::Oracle::db (DBI::db=HASH(0x98fa43c)~INNER 'PrintError'
> 1)
> <- STORE= 1 at DBI.pm line 645
> -> STORE for DBD::Oracle::db (DBI::db=HASH(0x98fa43c)~INNER 'AutoCommit'
> 0)
> <- STORE= 1 at DBI.pm line 645
> -> STORE for DBD::Oracle::db (DBI::db=HASH(0x98fa43c)~INNER 'Username'
> 'belkit')
> <- STORE= 1 at DBI.pm line 648
> <> FETCH= 'belkit' ('Username' from cache) at DBI.pm line 648
> <- connect= DBI::db=HASH(0x9a73550)
> -> STORE for DBD::Oracle::db (DBI::db=HASH(0x98fa43c)~INNER
> 'dbi_connect_closure' CODE(0x9a735ec))
> <- STORE= 1 at DBI.pm line 668
> -> prepare for DBD::Oracle::db (DBI::db=HASH(0x9a73550)~0x98fa43c '
> INSERT INTO test_long_str (id, str) VALUES (?, ?)
> ')
> dbd_preparse scanned 2 distinct placeholders
> <- prepare= DBI::st=HASH(0x98fa550) at utf_test4000.pl line 27
> UTF8 on, ASCII, 2075 characters 2075 bytes
> -> execute for DBD::Oracle::st (DBI::st=HASH(0x98fa550)~0x98a11a4 3 "
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaa...")
> bind :p1 <== 3 (type 0)
> bind :p1 <== 3 (size 1/2/0, ptype 5, otype 1)
> bind :p2 <== "
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaa..." (type 0)
> bind :p2 <== "
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaa..." (size 2075/2076/0, ptype 4, otype 1)
> dbd_st_execute INSERT (out0, lob0)...
> !! ERROR: '1461' 'ORA-01461: can bind a LONG value only for insert into
> a LONG column (DBD ERROR: error possibly near <*> indicator at char 19 in '
> INSERT INTO <*>test_long_str (id, str) VALUES (:p1, :p2)
> ')' (err#1)
> <- execute= undef at utf_test4000.pl line 36
> 1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x98a11a4)~INNER 'ParamValues')
> ERROR: '1461' 'ORA-01461: can bind a LONG value only for insert into
> a LONG column (DBD ERROR: error possibly near <*> indicator at char 19 in '
> INSERT INTO <*>test_long_str (id, str) VALUES (:p1, :p2)
> ')' (err#1)
> 1 <- FETCH= HASH(0x9ac97b0)2keys at utf_test4000.pl line 36
> DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for
> insert into a LONG column (DBD ERROR: error possibly near <*> indicator at
> char 19 in '
> INSERT INTO <*>test_long_str (id, str) VALUES (:p1, :p2)
> ') [for Statement "
> INSERT INTO test_long_str (id, str) VALUES (?, ?)
> " with ParamValues: :p1=3, :p2="
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaa..."] at utf_test4000.pl line 36.
> DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for
> insert into a LONG column (DBD ERROR: error possibly near <*> indicator at
> char 19 in '
> INSERT INTO <*>test_long_str (id, str) VALUES (:p1, :p2)
> ') [for Statement "
> INSERT INTO test_long_str (id, str) VALUES (?, ?)
> " with ParamValues: :p1=3, :p2="
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaa..."] at utf_test4000.pl line 36.
> -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x98a11a4)~INNER)
> ERROR: '1461' 'ORA-01461: can bind a LONG value only for insert into
> a LONG column (DBD ERROR: error possibly near <*> indicator at char 19 in '
> INSERT INTO <*>test_long_str (id, str) VALUES (:p1, :p2)
> ')' (err#1)
> <- DESTROY= undef
> -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x98fa43c)~INNER)
> Issuing rollback() for database handle being DESTROY'd without explicit
> disconnect().
> ERROR: '1461' 'ORA-01461: can bind a LONG value only for insert into
> a LONG column (DBD ERROR: error possibly near <*> indicator at char 19 in '
> INSERT INTO <*>test_long_str (id, str) VALUES (:p1, :p2)
> ')' (err#0)
> <- DESTROY= undef
> -- DBI::END
> -> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x9969f68)~0x9a10e54)
> <- disconnect_all= (not implemented) at DBI.pm line 677
> ! -> DESTROY in DBD::_::common for DBD::Oracle::dr
> (DBI::dr=HASH(0x9a10e54)~INNER)
> ! <- DESTROY= undef during global destruction
>
> ====== input data ==============
>
> <test>
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> </test>
>
>
>
>
> ------------------------------------------------------------------
> Teemu Kivioja, Research Scientist
> VTT Biotechnology
> P.O. Box 1500, FIN-02044 VTT, Finland
> (Street address: Tietotie 2, Espoo, Otaniemi)
> Email: [EMAIL PROTECTED]
> Phone: +358 20 722 7111
> Fax: +358 20 722 7071
