Creating my own implementation of DBI::Iterator in perl

Question

UPDATE:

Right now I am trying to take in to an array around 120M of rows. Reason I'm not doing this over UTL_file is because in our production server it requires oracle user access to write and move the written flat-file into our desired directory. Add in to the problem is that there are still a lot of text manipulations needed after extraction which I think is a job for Perl.

What I want to do right now is to write my own implementation of the DBI::Iterator and all other dependencies it may need; to be clear though, not necessarily all. (like some methods only of DBI, then just idb_rows... just to get this running since I cannot install modules)

Original Question:

Good day, I'm relatively new to perl programming, A week ago I started receiving out of memory messages again in perl. I was able to get around this before by switching to 64 bit perl.

I just found yesterday that the kernel of my production machine is not allowing me to use more than 4GB of memory (in my other production server I am able to load large amounts of data to memory)

Here are my specific restrictions in my production server

1. I am not allowed to install new modules of perl
2. I am allowed, in a way, to install them locally but I am having trouble

What I intend to do now is to recreate this module. Iterator::DBI

I have no background of iterators. for the longest time I develop database extractions and ETL processes through the function below. It's my 1st time to encounter an out of memory error again after a year and a half of using the function below.

sub myDBI
{
    my ($filename) = @_; 
    my $query = "";
    unless(open(FILE,$filename))
    {
        Logger("[ ERR ] unable to open $SETTINGS{SQL_TRIGGER}\n");
        print
        die;
    }
    my @result=`sqlplus -S $SETTINGS{USER_NAME}/$SETTINGS{PASSWORD}\@$SETTINGS{DB_NAME} <<END
    SET HEADING OFF
    SET FEEDBACK OFF
    SET SERVEROUTPUT ON
    SET LINES 5000
    SET COLSEP "||"
    $query
    /
    `
    ;
    @result;
}

Answer 1

You have several options:

• If you have local::lib installed, you can install CPAN modules like Iterator::DBI to a user directory. You'll just need to set some environment variables to specify which directory to use.

  export PERL_MB_OPT='--install_base /home/username/perl5'
  export PERL_MM_OPT='INSTALL_BASE=/home/username/perl5'
  export PERL5LIB='/home/username/perl5/lib/perl5/i386-linux:/home/username/perl5/lib/perl5'
  export PATH="/home/username/perl5/bin:$PATH"
  

• You actually don't need Iterator::DBI. That module just wraps an Iterator object around a DBI statement handle, which is an iterator itself. So you can just use DBI directly to connect to the database. (Note that either way, you'll connect to the database directly rather than going through sqlplus.)

  use DBI;
  my $dbh = DBI->connect(...);
  my $sth = $dbh->prepare($sql_query);
  $sth->execute(@params);
  # iterate
  while (my $row = $sth->fetchrow_arrayref) {
      ...
  }
  

• If you really want to use Iterator::DBI and you can't install the module, you can just copy the source code directly and put it in ./Iterator/DBI.pm relative to your application. But the problem with this is you'll need to get around the dependencies. To do that I would replace exceptions with a simple die or croak, and replace Iterator with a closure (see chapter 5 of Higher Order Perl for how to do this). This option looks quite difficult for a beginner Perl programmer.

• If you really can't get DBI working, you can pipe the sqlplus output to a file and iterate through the file.