No you are not missing 64k seems to be the limit for that interface with
Clobs and it is a Historical Oracle limit not Perl limit.
in DBD::ORacle 1.24
http://search.cpan.org/~pythian/DBD-Oracle-1.24a/Oracle.pm#Simple_Fetch_for_CLOBs_and_BLOBs
I say the following
For CLOBs and NCLOBs the limit is 64k chars if there is no truncation,
this is an internal OCI limit complain to them if you want it changed.
However if you CLOB is longer than this and also larger than the
'LongReadLen' than the 'LongReadLen' in chars is returned.
It seems with BLOBs you are not limited by the 64k.
Spent a long while trying to get more that 64k out of it with no luck.
Not 100% why this 64k limit but if you find some way around it that
would be great.
James Hooker wrote:
Hi,
I'm using DBI 1.608, DBD::Oracle 1.23, OCI from 10.2.0.2. I'm trying to select
a CLOB having prepared my select using ora_pers_lob = 1. I've also got the
following options set on my DB handle:
LongReadLen => 100 * 1024,
LongTruncOk => 1,
ora_ph_type => ORA_CHAR, # Default of VARCHAR2 strips whitespace
The result returned seems to be truncated at 64K no matter what I set
LongReadLen to. I went poking around in the oci8.c file which is part of
DBD::Oracle and found a comment like:
/* very special case for binary lobs that are directly fetched. Seems I have to
use SQLT_LVB to get the length all other will fail*/
I also see a comment in the DBD documentation:
http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#___top
"For CLOBs and NCLOBs the limit is 64k chars if there is no truncation,
this is an internal OCI limit complain to them if you want it changed.
However if you CLOB is longer than this and also larger than the
'LongReadLen' than the 'LongReadLen' in chars is returned."
The two comments above, taken together, make me suspect that simple CLOB
handling via the data interface for persistent LOBs has some kind of historical
baggage attached to it and today is still not fully supported/working within
DBI/DBD (this is not the case for BLOBs though?). Does that sound correct or
am I missing something here to make this work for LOBs larger than 64K?
Thanks in advance for any hints and tips you might be able to provide.
Jim.
