| Date: | Wed, 03 Dec 2003 15:55:49 -0800 |
| From: | Jay Turner <J.Turner [...] mdl.com> |
| To: | bug-DBI [...] rt.cpan.org |
| Subject: | Perl DBI bug handling CLOBs |
Date: Fri, 28 Feb 2003 16:55:28 -0800
It has come to my attention that PERL DBI counts on OCILobGetLength
returning BYTES. It returns CHARACTERS instead, which is the count of
variable-width characters. For multi-byte character sets this results
in errors such as:
DBD::Oracle::st fetch failed: ORA-03130: the buffer for the next piece
to be fetched is required (DBD ERROR: OCILobGetLength) at id rmsc01.pl
line 294.
The correct way to read CLOBs is
1) Query the LOB locator for the CSID and CSFRM (character set ID and
form). A character set >= 800 is a mulitbyte character set and csfrm
<> 0 is CLOB.
2) Pass the CSID and CSFRM to OCILobRead with AMT=0 and pass your
buffer address and size.
3) Your callback routine must either be capable of completing the I/O
by allocating additional buffers, or it must notify the caller of
OCILobRead to free the lob locator, since an incomplete read jams the
locator-you can't use it for anything else without finishing the read
(attempts to reuse the locator will result in errors).
Likewise, with OCILobWrite, you have to pass the CSID and CSFRM, with
AMT=0 and the buffer size in bytes. The callback can just say it has
zero bytes and set piece=OCI_LAST_PIECE.
You cannot use the return value of OCILobGetLength as the size of the
data that is being read. The actual size of the data is unknown for
variable-width characters, and the buffer has to be big enough to
accomplish the translation, so you can't just double or triple the
return value from OCILobGetLength (I have seen that approach fail).
You can simulate the effects of a foreign character set by
$ export NLS_LANG=Japanese