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
