Updating a CLOB using DBI

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I'm need some help! I'm trying to update a CLOB using DBI and DBD:ORACLE....

        my $sql = "update list set clist=? where name='$TABLE_NAME'";
        my $sth = $dbh->prepare( $sql );
        $sth->bind_param(1, $xlist, { ora_type => ORA_CLOB });
        $sth->execute($xlist);
[download]

The code works but it's only updating the first 79 characters... it cuts everything off after that. Thanks for your help.

Comment on Updating a CLOB using DBI Download Code

Replies are listed 'Best First'.
Re: Updating a CLOB using DBI by davidrw (Prior) on Nov 22, 2005 at 04:40 UTC
Try setting the `LongReadLen` attribute of `$dbh` .. see DBI docs for details (not sure what value you need for your purposes--i arbitrarily chose 8000). `my $dbh = DBI->connect($data_source, $username, $auth, { LongReadLen=> +8000 } ); # OR $dbh->{LongReadLen} = 8000;` [download] See also Working with CLOB	[reply] [d/l] [select]
Re^2: Updating a CLOB using DBI by ChemBoy (Priest) on Nov 22, 2005 at 20:28 UTC
LongReadLen won't (as the OP seems to have discovered) help here: The `LongReadLen` attribute only relates to fetching and reading long values; it is not involved in inserting or updating them. I've never done anything with CLOBs either, but it strikes me that the `bind` and `execute` method calls both do the same thing--it might be worth either taking out the `bind_param` call or (more likely) removing the unnecessary argument from `execute`, so it's just so: `my $sth = $dbh->prepare( $sql ); $sth->bind_param(1, $xlist, { ora_type => ORA_CLOB }); $sth->execute;` [download] If that works, my guess would be that the information from the first binding didn't make it to the database side before it was overridden, so the information that the variable was a CLOB went missing. If it doesn't, then I'm just as lost as AnonyMonk. If God had meant us to fly, he would never have given us the railroads. --Michael Flanders	[reply] [d/l]
Re^2: Updating a CLOB using DBI by Anonymous Monk on Nov 22, 2005 at 06:25 UTC
Tried that... it didn't seem to help. 79 characters that's it.	[reply]