Re: Inserting UTF-8 string over 2000 chars to Oracle 10g - error ORA-01461

2005-08-13 Thread Jared Still 
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

Inserting UTF-8 string over 2000 chars to Oracle 10g - error ORA-01461

2005-07-29 Thread Teemu Kivioja 
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

Re: Inserting UTF-8 string over 2000 chars to Oracle 10g - error ORA-01461

2005-07-29 Thread Teemu Kivioja 

Sorry,

Small, yet crucial error in my mail

At 11:00 29.7.2005 +0300, you wrote:

- setting NLS_LANG to various values does seem to affect the behavior.


I meant to write

- setting NLS_LANG to various values does _not_ seem to affect the behavior.

Teemu Kivioja


--
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