Perl DBI Alternative to LongReadLen

Question

I would like to know the most memory efficient way to infer arbitrarily large data fields from Oracle db using Perl DBI. The method I know to use is to set the LongReadLen attribute in the database descriptor to something big enough. However, my application needs to pull out several thousand records, so doing this in arbitration is extremely inefficient.

doc suggests executing the query in advance to find the highest potential value and set it.

$dbh->{LongReadLen} = $dbh->selectrow_array(qq{ SELECT MAX(OCTET_LENGTH(long_column_name)) FROM table WHERE ... }); $sth = $dbh->prepare(qq{ SELECT long_column_name, ... FROM table WHERE ... }); 

However, this is still inefficient, since the remote data is not representative for each record. The largest values ​​exceed MB, but the average record is less than KB. I want to be able to pull out all the information information (i.e., without truncation), losing as little memory as possible on unused buffers.

The method I examined is to pull out the data in pieces of, say, 50 time records and set LongReadLen against the maximum length of the records of this fragment. Another work that could, but not necessarily, be based on the idea of ​​a piece, would be to deploy a child process, extract the data, and then kill the child (by wasting memory with him). The great thing is the ability to force free DBI buffers, but I don't think it is possible.

Has anyone addressed a similar issue with success? Thanks for the help!

EDIT

Perl v5.8.8, DBI v1.52

To clarify: memory inefficiency comes from using "LongReadLen" along with {ora_pers_lob => 1} in preparation. Using this code:

 my $sql = "select myclob from my table where id = 68683"; my $dbh = DBI->connect( "dbi:Oracle:$db", $user, $pass ) or croak $DBI::errstr; print "before"; readline( *STDIN ); $dbh->{'LongReadLen'} = 2 * 1024 * 1024; my $sth = $dbh->prepare( $sql, {'ora_pers_lob' => 1} ) or croak $dbh->errstr; $sth->execute() or croak( 'Cant execute_query '. $dbh->errstr . ' sql: ' . $sql ); my $row = $sth->fetchrow_hashref; print "after"; readline( *STDIN ); 

The memory usage of resident memory "before" is 18 MB, and the use of "after" is 30 MB. This is not valid for a large number of requests.

Answer 1

Are your columns large LOB data (CLOB or BLOB)? If so, you do not need to use LongReadLen at all; DBD :: Oracle provides a large-capacity stream interface.

What you want to do is bind the parameter as type ORA_CLOB or ORA_BLOB , which will provide you with a "large object locator" "returned from the request instead of tex. Then you use ora_lob_read along with the LOB locator to get the data. Here is an example of code that worked for me:

 sub read_lob { my ( $dbh, $clob ) = @_; my $BLOCK_SIZE = 16384; my $out; my $offset = 1; while ( my $data = $dbh->ora_lob_read( $clob, $offset, $BLOCK_SIZE ) ) { $out .= $data; $offset += $BLOCK_SIZE; } return $out; } 

Answer 2

I think of it this way:

 use Parallel::ForkManager use strict; # Max 50 processes for parallel data retrieving my $pm = new Parallel::ForkManager(50); # while loop goes here while (my @row = $sth->fetchrow_array) { # do the fork $pm->start and next; # # Data retreiving goes here # # do the exit in the child process $pm->finish; } $pm->wait_all_children; 

check out Parallel :: ForkManager in CPAN to learn more.