Monday, February 16, 2015

Inserting and selecting CLOBs with DBD::Oracle

Here's a table with a CLOB:
create table tq84_lob ( id number primary key, c clob )

With Perl and DBD::Oracle, the CLOB in the table can be filled like so:

my $sth = $dbh -> prepare(q{ insert into tq84_lob values ( 1, empty_clob() ) }); # setting ora_auto_lob to false: # fetch the «LOB Locator» instead of the CLOB # (or BLOB) content: my $c = $dbh -> selectrow_array( "select c from tq84_lob where id = 1 for update", {ora_auto_lob => 0} ); $dbh -> ora_lob_write( $c, 1, # offset, starts with 1! join '-', (1 .. 10000) );

A CLOB can be selected like so:

my $c = $dbh -> selectrow_array( "select c from tq84_lob where id = 1", {ora_auto_lob => 0}); my $count = 0; while (my $buf = $dbh->ora_lob_read($c, 1+$count*1000, 1000)) { print $buf; $count++; }

No comments:

Post a Comment