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
