Hi there,
This is a summary of some of my efforts to get LOB transfers working
with DBI.
I've seen several posts in dbi-users about problems fetching LOBs with
DBD::Oracle. One case, which I couldn't get to work is an assignment to
a bind variable of type CLOB inside a PL/SQL block:
DECLARE
myclob CLOB;
BEGIN
SELECT soandso.getClobVal() INTO myclob FROM whatever;
? := c;
END;
AFAIK, the correct binding in perl should be:
my $result;
$sth->bind_param_inout(1, \$result, $maxlen,
{ ora_type => ORA_CLOB }); # or ORA_BLOB resp.
which in my environment always causes a segmentation fault, regardless
of the returned object's size. You can work around this problem by
wrapping your PL/SQL code in a FUNCTION and fetching the LOB via SELECT
statement:
SELECT getLargeAmountOfData() FROM DUAL;
Using fetchrow with appropriate LongReadLen and LongTruncOk settings
works for me. You can also use the (undocumented?) DBI function
blob_read($rownum, $offset, $length):
$sth->fetchrow();
my $offset = 0;
while( defined(my $chunk = $sth->blob_read(0, $offset, 16384) )) {
if (my $error = $sth->errstr) { warn $error; }
$offset += length $chunk;
print $chunk;
}
This should solve the downloading problem. Uploading large amounts of
data is still a problem to me. There were two other posts about
inserting XMLType data, that pretty much describe the problem. For
example statements like
SELECT XMLType(?) FROM DUAL;
with ? bound as ORA_CLOB yield 'Table or view does not exist' or
'Invalid LOB locator'. I've tried GLOBAL TEMPORARY TABLES and a CLOB
table with INSERT TRIGGER without any luck. The only thing that seems to
work is to store the LOB inside a (non-temporary) table and then doing
manipulation on that table column, which of course raises threading
problems.
OK, that's all for now
cheers,
Hendrik
--
hendrik fu�
morphochem AG
gmunder str. 37-37a
81379 muenchen
