title: Perl DBI alternative to LongReadLen
body: <p>I'd like to know the most memory-efficient way to pull arbitrarily large data fields from an Oracle db with Perl DBI.  The method I know to use is to set the 'LongReadLen' attribute on the database handle to something sufficiently large.  However, my application needs to pull several thousand records, so doing this arbitarily is extremely memory inefficient.</p>
  
  <p>The <a href="http://search.cpan.org/~timb/DBI-1.616/DBI.pm#LongReadLen" rel="noreferrer">doc</a> suggests doing a query upfront to find the largest potential value, and setting that.</p>
  
  <pre><code>$dbh-&gt;{LongReadLen} = $dbh-&gt;selectrow_array(qq{
      SELECT MAX(OCTET_LENGTH(long_column_name))
      FROM table WHERE ...
  });
  $sth = $dbh-&gt;prepare(qq{
      SELECT long_column_name, ... FROM table WHERE ...
  });
  </code></pre>
  
  <p>However, this is still inefficient, since the outlying data is not representative of every record.  The largest values are in excess of a MB, but the average record is less than a KB. I want to be able to pull all of the informatoin (i.e., no truncation) while wasting as little memory on unused buffers as possible.</p>
  
  <p>A method I've considered is to pull the data in chunks, say 50 records a time, and set LongReadLen against the max length of records of that chunk.   Another work around, which could, but doesn't have to, build on the chunk idea, would be to fork a child process, retrieve the data, and then kill the child (taking the wasted memory with it).  The most wonderful thing would be the ability to force-free the DBI buffers, but I don't think that's possible.</p>
  
  <p>Has anyone addressed a similar problem with any success?  Thanks for the help!</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Perl v5.8.8, DBI v1.52</p>
  
  <p>To clarify: the memory inefficiency is coming from using 'LongReadLen' together with {ora_pers_lob => 1} in the prepare.  Using this code:</p>
  
  <pre><code>my $sql = "select myclob from my table where id = 68683";
  my $dbh = DBI-&gt;connect( "dbi:Oracle:$db", $user, $pass ) or croak $DBI::errstr;
  
  print "before";
  readline( *STDIN );
  
  $dbh-&gt;{'LongReadLen'} = 2 * 1024 * 1024;
  my $sth = $dbh-&gt;prepare( $sql, {'ora_pers_lob' =&gt; 1} ) or croak $dbh-&gt;errstr;
  $sth-&gt;execute() or croak( 'Cant execute_query '. $dbh-&gt;errstr . ' sql: ' . $sql );
  my $row = $sth-&gt;fetchrow_hashref;
  
  print "after";
  readline( *STDIN );
  </code></pre>
  
  <p>Resident memory usage "before" is at 18MB and usage "after" is at 30MB.  This is unacceptable over a large number of queries.</p>