#!/usr/bin/perl 
use warnings;
use strict;
use DBI;
use DBD::ODBC;


# ============================================================
# sample to illustrate strange behaviour on solaris 2.7
# (does not occur on solaris 2.8)
# (db names have been change to protect the innocent??)
#
# Using perl 5.8.2
# Using DBI 1.38
# Using DBD:ODBC 1.06
# Using Openlink ODBC driver (version 5.x) for Oracle 8 on Solaris 2.7
# ============================================================
# DEFINITION OF STRANGE BEHAVIOUR:
#
# when running this program via statement
#
# 'perl mem_sample.pl my_db my_name my_password'
#
#    * total memory usage: 125 Meg
#          approx 1.75Meg per SECOND!
#    * total elapsed time:  73 sec
#
#
# when running this program via statement
#
# 'perl -d:DProf mem_sample.pl my_db my_name my_password'
#
#    * total memory usage:   5 Meg
#    * total elapsed time:  36 sec
#


# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# this is the part that causes problems
# - comment out   'use POSIX'  
#              or 'use CWD'
#              or '...glob'
#   and the effect will go away.
#   
#  NOTE:  Notice that 'Problem_Routine' is never actually 
#         called.
# ------------------------------------------------------------     
    use POSIX ":termios_h";
    use Cwd 'abs_path';   
sub Problem_Routine($\@;\@) {
    my $b_err_file;
    my @b_err_file = glob("./$b_err_file");
}
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    


# ------------------------------------------------------------     
# globals
# ------------------------------------------------------------     
    my %firsttable;

    # inputs
    my $oracle_db = shift;
    my $db_name = shift;
    my $username = shift || "";
    my $password = shift || "";

    my $start = `date`;
    
# ------------------------------------------------------------     
# Connect to Oracle.
# ------------------------------------------------------------     
    my $dbh = DBI->connect
         ( "DBI:ODBC:$oracle_db", 
           "$username", 
           "$password" )
      or die(   "\n "
              . "===========================\n"
              . "Could not make connection to database:\n"
              . " $DBI::errstr\n"
              . "===========================\n" );
    print "\nConnected to Oracle.\n";

    # Die on any error in an embedded SQL statement (SLB)
    $dbh->{RaiseError} = 1;

# ------------------------------------------------------------     
# Define queries
# ------------------------------------------------------------     
    # Define query for top level
    my $sql_statement =
        "SELECT col_one col_two\n"
      . "FROM   $db_name.MYTABLE1 A\n";
      
    my $top_level = $dbh->prepare($sql_statement);
    
    # Define query for second level
    $sql_statement = 
        "SELECT b.col_three, b.col_four\n"
      . "FROM $db_name.MYTABLE1 A, $db_name.MYTABLE2 B\n"
      . "WHERE b.col_one = ? and a.col_one = b.col_one\n";
    
    my $second_level = $dbh->prepare($sql_statement);
    

# ------------------------------------------------------------     
# execute top level queries (~15,000 rows)
# ------------------------------------------------------------     
    my $rows_processed = 0;
    my $rows = [];
    my $max_size = 5000;
    my $ret = $max_size;

    $top_level->execute();
    while (   ($ret == $max_size) 
           && ($rows = $top_level->fetchall_arrayref(undef,$ret))) {
        $ret = scalar(@$rows);

        while (my $row = shift @$rows) {
            @firsttable{qw(label word)} = @$row;
            $rows_processed++;
            
            get_second_table_info();
        }
    }
    print "\nQuery returned $rows_processed rows.\n\n";
           
    print "Start: $start\nEnd: ",`date`,"\n";            
    exit 0;
    
# ----------------------------------------------------
# get second table info
# (on average < 2 rows per query: max 10)
# ----------------------------------------------------
    sub get_second_table_info {

        $second_level->execute($firsttable{label});

        my @col_three = ();
        my @col_four = ();
        my $i=0;
        my $rows;

        $rows = $second_level->fetchall_arrayref();
        while (my $row = shift(@$rows)) 
        {
            (
              $col_three[$i],  $col_four[$i]
            )
            = ($row->[0],$row->[1]);
            # ---> do some processing here
        }
        $i++;
        
        # ---> do some more processing here
        return;
    }
[download]