I've been using the Perl DBI/DBD::Oracle modules with CLOB types for some time, but without specifying that the placeholder is a CLOB type. This works fine, until the data input exceeds 32K, the max buffer size for a VARCHAR2 (at least, this is how I've understood it, please correct me if it's wrong ;).
I've been told that if I bind the input placeholder with a CLOB type, I can work with values whose size exceeds the VARCHAR2 limitation. So, I've been testing this. And, it works, but generates this error:
(in cleanup) DBD::Oracle::st DESTROY failed: (DBD (UNKNOWN OCI STATUS 1041): OCILobFreeTemporary) [for Statement "
BEGIN
append_comments(:incident, :comments);
END;
"], <> line 3.
The "<> line3." is the number of lines of input, in this case only 3 (for this test), but I've also done one with 6708 lines (264843 chars). I also have no idea if this is a problem in my Perl code, the DBI/DBD modules, an issue with OCI or something to do with the PL/SQL stored procedure (append_comments). Any pointers on how to determine that?
I did some searching, but could only find one possible related issue:
1379782
Which seems to have been fixed, but in a .NET package, which is not relevant to my environment.
Any help, suggestions and pointers welcome ;)
Details:
Perl version: v5.8.8, for i686-linux
DBI version: 1.609
DBD::Oracle version: 1.18
The Perl code:
#!/usr/local/etrack/perl/bin/perl -w
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
...
$dbHandle = creatDBHandle('OraDBName');
my($cursor, $newIncident, $comments);
$comments = join '', <>;
$newIncident = '1666233';
$cursor = $dbHandle->prepare(q(
BEGIN
append_comments(:incident, :comments);
END;
));
$cursor->bind_param(":incident", $newIncident);
$cursor->bind_param(":comments", $comments,{ora_type => ORA_CLOB});
$cursor->execute();
$dbHandle->commit;
$dbHandle->disconnect;
The above actually works, appending whatever is in $comments to the existing comments CLOB.