On Thu, Feb 26, 2004 at 12:29:29PM +1000, [EMAIL PROTECTED] wrote:
>
> I am sure this has been covered but I am a newbie to both DBI::Oracle and
> BLOBs. I have done a few Google searches and have found several useful code
> example on how to write to a BLOB datatype but nothing that shows me a
> technique for reading BLOB data and storing it into a file.
>
> What I have are several BLOB columns containing XML code that has been
> zipped. I want to retrieve the BLOB and store it in a file to be unzipped
> and then compared to the original for testing.
Make sure you've got DBD::Oracle 1.15, then, if you read the docs,
you'll find:
=head2 LOB Locator Methods
The following driver-specific methods let you manipulate "LOB Locators".
LOB locators can be selected from tables directly, if the C<ora_auto_lob>
attribute is false, or returned via PL/SQL procedure calls.
(If using a DBI version earlier than 1.36 they must be called via the
func() method. Note that methods called via func() don't honour
RaiseError etc, and so it's important to check $dbh->err after each call.
It's recommended that you upgrade to DBI 1.38 or later.)
B<Warning:> Currently multi-byte character set issues have not been
fully worked out. So these methods may not do what you expect if
either the perl data is utf8 or the CLOB is a multi-byte character set
(including uft8). The current behaviour in these situations may not be
correct and is B<subject to change>. I<Testing and patches are most welcome.>
=over 4
=item ora_lob_read
$data = $dbh->ora_lob_read($lob_locator, $offset, $length);
Read a portion of the LOB. $offset starts at 1.
Uses the Oracle OCILobRead function.
=item ora_lob_write
$rc = $dbh->ora_lob_write($lob_locator, $offset, $data);
Write/overwrite a portion of the LOB. $offset starts at 1.
Uses the Oracle OCILobWrite function.
=item ora_lob_append
$rc = $dbh->ora_lob_append($lob_locator, $data);
Append $data to the LOB.
Uses the Oracle OCILobWriteAppend function.
=item ora_lob_trim
$rc = $dbh->ora_lob_trim($lob_locator, $length);
Trims the length of the LOB to $length.
Uses the Oracle OCILobTrim function.
=item ora_lob_length
$length = $dbh->ora_lob_length($lob_locator);
Returns the length of the LOB.
Uses the Oracle OCILobGetLength function.
=back
=head2 LOB Locator Method Examples
I<Note:> Make sure you first read the note in the section above about
multi-byte character set issues with these methods.
The following examples demonstrate the usage of LOB Locators
to read, write, and append data, and to query the size of
large data.
The following examples assume a table containing two large
object columns, one binary and one character, with a primary
key column, defined as follows:
CREATE TABLE lob_example (
lob_id INTEGER PRIMARY KEY,
bindata BLOB,
chardata CLOB
)
It also assumes a sequence for use in generating unique
lob_id field values, defined as follows:
CREATE SEQUENCE lob_example_seq
=head2 Example: Inserting a new row with large data
Unless enough memory is available to store and bind the
entire lob data for insert all at once, the lob columns must
be written iteratively, piece by piece. In the case of a new row,
this is performed by first inserting a row, with empty values in
the lob columns, then modifying the row by writing the large data
iteratively to the lob columns using their LOB locators as handles.
The insert statement must create token values in the lob
columns. Here, we use the empty string for both the binary
and character large object columns 'bindata' and 'chardata'.
After the INSERT statement, a SELECT statement is used to
acquire lob locators to the 'bindata' and 'chardata' fields
of the newly inserted row. Because these lob locators are
subsequently written, they must be acquired from a select
statement containing the clause 'FOR UPDATE' (lob locators
are only valid within the transaction that fetched them, so
can't be used effectively if AutoCommit is enabled).
my $lob_id = $dbh->selectrow_array( <<" SQL" );
SELECT lob_example_seq.nextval FROM DUAL
SQL
my $sth = $dbh->prepare( <<" SQL" );
INSERT INTO lob_example
( lob_id, bindata, chardata )
VALUES ( ?, ?, ? )
SQL
$sth->execute( $lob_id, '', '' );
$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT bindata, chardata
FROM lob_example
WHERE lob_id = ?
FOR UPDATE
SQL
$sth->execute( $lob_id );
my ( $bin_locator, $char_locator ) = $sth->fetchrow_array();
$sth->finish();
open BIN_FH, "/binary/data/source" or die;
open CHAR_FH, "/character/data/source" or die;
my $chunk_size = 4096; # Arbitrary chunk size
# BEGIN WRITING BIN_DATA COLUMN
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = '';
while( $length = read( BIN_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write( $bin_locator, $offset, $length );
$offset += $length;
}
# BEGIN WRITING CHAR_DATA COLUMN
$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, $length );
$offset += $length;
}
In this example we demonstrate the use of ora_lob_write()
iteratively 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.
=head2 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".
=cut
Of course there isn't an actual example of "reading BLOB data and
storing it into a file" but I'm sure you'll be able to work out how to
use ora_lob_read to do that.
A patch to the docs to add an example would be welcome.
Tim.
