view release on metacpan or  search on metacpan

README.mkdn  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.

### 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".

### 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.

### 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() ) {