view release on metacpan or  search on metacpan

lib/DBD/Oracle.pm  view on Meta::CPAN

   while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
      $dbh->ora_lob_write( $bin_locator, $offset, $buffer );
      $offset += $length;
   }

   # BEGIN WRITING CHAR_DATA COLUMN
   $chunk_size = $dbh->ora_lob_chunk_size( $char_locator );
   $offset = 1;   # Offsets start at 1, not 0
   $length = 0;
   $buffer = '';
   while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
      $dbh->ora_lob_write( $char_locator, $offset, $buffer );
      $offset += $length;
   }

In this example we demonstrate the use of ora_lob_write()
interactively to append data to the columns 'bin_data' and
'char_data'.  Had we used ora_lob_append(), we could have
saved ourselves the trouble of keeping track of the offset
into the lobs.  The snippet of code beneath the comment
'BEGIN WRITING BIN_DATA COLUMN' could look as follows:

   my $buffer = '';
   while ( read( BIN_FH, $buffer, $chunk_size ) ) {
      $dbh->ora_lob_append( $bin_locator, $buffer );
   }

The scalar variables $offset and $length are no longer
needed, because ora_lob_append() keeps track of the offset
for us.

=head3 Example: Updating an existing row with large data

In this example, we demonstrate a technique for overwriting
a portion of a blob field with new binary data.  The blob
data before and after the section overwritten remains
unchanged.  Hence, this technique could be used for updating
fixed length subfields embedded in a binary field.

   my $lob_id = 5;   # Arbitrary row identifier, for example

   $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT bindata
      FROM lob_example
      WHERE lob_id = ?
      FOR UPDATE
   SQL
   $sth->execute( $lob_id );
   my ( $bin_locator ) = $sth->fetchrow_array();

   my $offset = 100234;
   my $data = "This string will overwrite a portion of the blob";
   $dbh->ora_lob_write( $bin_locator, $offset, $data );

After running this code, the row where lob_id = 5 will
contain, starting at position 100234 in the bin_data column,
the string "This string will overwrite a portion of the blob".

=head3 Example: Streaming character data from the database

In this example, we demonstrate a technique for streaming
data from the database to a file handle, in this case
STDOUT.  This allows more data to be read in and written out
than could be stored in memory at a given time.

   my $lob_id = 17;   # Arbitrary row identifier, for example

   $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT chardata
      FROM lob_example
      WHERE lob_id = ?
   SQL
   $sth->execute( $lob_id );
   my ( $char_locator ) = $sth->fetchrow_array();

   my $chunk_size = 1034;   # Arbitrary chunk size, for example
   my $offset = 1;   # Offsets start at 1, not 0
   while(1) {
      my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size );
      last unless length $data;
      print STDOUT $data;
      $offset += $chunk_size;
   }

Notice that the select statement does not contain the phrase
"FOR UPDATE".  Because we are only reading from the LOB
Locator returned, and not modifying the LOB it refers to,
the select statement does not require the "FOR UPDATE"
clause.

A word of caution when using the data returned from an ora_lob_read in a conditional statement.
for example if the code below;

   while( my $data = $dbh->ora_lob_read( $char_locator, $offset, $chunk_size ) ) {
        print STDOUT $data;
        $offset += $chunk_size;
   }

was used with a chunk size of 4096 against a blob that requires more than 1 chunk to return
the data and the last chunk is one byte long and contains a zero (ASCII 48) you will miss this last byte
as $data will contain 0 which PERL will see as false and not print it out.

=head3 Example: Truncating existing large data

In this example, we truncate the data already present in a
large object column in the database.  Specifically, for each
row in the table, we truncate the 'bindata' value to half
its previous length.

After acquiring a LOB Locator for the column, we query its
length, then we trim the length by half.  Because we modify
the large objects with the call to ora_lob_trim(), we must
select the LOB locators 'FOR UPDATE'.

   my $sth = $dbh->prepare( <<"   SQL", { ora_auto_lob => 0 } );
      SELECT bindata
      FROM lob_example
      FOR UPATE
   SQL
   $sth->execute();
   while( my ( $bin_locator ) = $sth->fetchrow_array() ) {