$ ./bm_foo.pl -N=10000
Benchmark: timing 10000 iterations of insert_and_select_foo_ora_sp, insert_and_select_foo_ora_sql...
insert_and_select_foo_ora_sp: 30 wallclock secs (10.17 usr +  0.38 sys = 10.55 CPU) @ 947.87/s (n=10000)
insert_and_select_foo_ora_sql: 20 wallclock secs ( 5.41 usr +  0.24 sys =  5.65 CPU) @ 1769.91/s (n=10000)

##</code><code>##

Total Elapsed Time = 30.88130 Seconds
  User+System Time = 10.92130 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 42.0   4.590  5.580  20000   0.0002 0.0003  DBI::st::execute
 13.7   1.500  1.500  20000   0.0001 0.0001  DBI::st::fetchrow_array
 8.97   0.980  0.980  50000   0.0000 0.0000  DBI::st::bind_param
 8.97   0.980  7.840  10000   0.0001 0.0008  main::get_foo_ora_sp
 6.23   0.680  1.170  20000   0.0000 0.0001  DBI::st::bind_param_inout
 5.95   0.650  3.050  10000   0.0001 0.0003  main::insert_foo_ora_sp
 3.75   0.410  0.410  19998   0.0000 0.0000  DBI::common::DESTROY
 3.20   0.350  0.350  10004   0.0000 0.0000  DBI::_setup_handle
 2.47   0.270  0.990  10002   0.0000 0.0001  DBI::_new_sth
 2.47   0.270  0.710  10004   0.0000 0.0001  DBI::_new_handle
 1.74   0.190  0.190  10000   0.0000 0.0000  main::update_foo_vars
 1.74   0.190 11.270  10000   0.0000 0.0011  main::__ANON__
 0.82   0.090 11.360  10002   0.0000 0.0011  Benchmark::__ANON__
 0.82   0.090  0.090  10004   0.0000 0.0000  DBI::st::TIEHASH
 0.73   0.080  0.080   9999   0.0000 0.0000  DBD::_mem::common::DESTROY

##</code><code>##

Total Elapsed Time = 20.02347 Seconds
  User+System Time = 6.393479 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 58.4   3.735  3.735  30000   0.0001 0.0001  DBI::st::execute
 15.7   1.005  1.005  30000   0.0000 0.0000  DBI::st::fetchrow_array
 10.4   0.670  3.565  10000   0.0001 0.0004  main::insert_foo_ora_sql
 8.76   0.560  2.415  10000   0.0001 0.0002  main::get_foo_ora_sql
 4.85   0.310  6.455  10000   0.0000 0.0006  main::__ANON__
 2.58   0.165  0.165  10000   0.0000 0.0000  main::update_foo_vars
 1.25   0.080  0.149      9   0.0089 0.0165  main::BEGIN
 0.55   0.035  6.490  10002   0.0000 0.0006  Benchmark::__ANON__
 0.31   0.020  0.020      4   0.0050 0.0050  DynaLoader::dl_load_file
 0.16   0.010  0.010      1   0.0100 0.0100  DBD::Oracle::db::_login
 0.16   0.010  0.010      3   0.0033 0.0033  DBD::Oracle::st::_prepare
 0.16   0.010  0.010      4   0.0025 0.0025  DynaLoader::dl_load_flags
 0.16   0.010  0.010      8   0.0012 0.0012  Pod::Parser::BEGIN
 0.16   0.010  0.020      6   0.0017 0.0033  Pod::Text::BEGIN
 0.16   0.010  0.010     23   0.0004 0.0004  vars::import

##</code><code>##

#!/usr/bin/perl

use strict;
use warnings;

use Devel::DProf;

use Benchmark;
use Getopt::Long;
use Pod::Usage;
use DBI;
use DBD::Oracle qw(:ora_types);

my %foo = (
  lnum1 => 123456789,
  lnum2 => 123456789,
  lnum3 => 123456789,
  lnum4 => 123456789,
);

my %sths = ();
my $dbh = undef;

##################################################################
# Insert foo using stored procedure (PL/SQL)
sub insert_foo_ora_sp {
##################################################################
  my $fooref = shift;

  # ------ prepare function call unless in cache
  if (!defined($sths{insert_foo_ora_sp})) {
    my $prepstr =
      q{BEGIN :retval := pkg_foo.func_insert_foo}.
      q{(:lnum1,:lnum2,:lnum3,:lnum4); END;};
    $sths{insert_foo_ora_sp} = $dbh->prepare ($prepstr);
  }
  if (!defined($sths{insert_foo_ora_sp})) {
    die(q{sth_prepare failed!});
  }

  my $retval = undef;
  $sths{insert_foo_ora_sp}->bind_param_inout(":retval", \$retval, 32);
  $sths{insert_foo_ora_sp}->bind_param(q{:} . $_, $fooref->{$_}) for (keys %{$fooref});

  # ------ execute
  my $rv = $sths{insert_foo_ora_sp}->execute();
  if ($rv != 1) {
    die(qq{sth_execute returned '$rv'});
  }

  return $retval;
}

##################################################################
# Insert foo using sql (using bound sql)
sub insert_foo_ora_sql {
##################################################################
  my $fooref = shift;

  if (!defined($sths{get_next_foo_id_sql})) {
    my $prepstr = qq{SELECT FOO_ID_SEQ.NEXTVAL FROM DUAL};
    $sths{get_next_foo_id_sql} = $dbh->prepare($prepstr);
  }
  if (!defined($sths{get_next_foo_id_sql})) {
    die(q{sth_prepare failed!});
  }

  my $rv = $sths{get_next_foo_id_sql}->execute();
  die(q{sth_execute failed:} . $!) unless $rv;

  my @arr = $sths{get_next_foo_id_sql}->fetchrow_array();


  push @arr, $foo{lnum1}, $foo{lnum2}, $foo{lnum3}, $foo{lnum4};

  if (!$sths{insert_foo_ora_sql}) {
    my $sql = "insert into foo (foo_id,lnum1,lnum2,lnum3,lnum4) values (?,?,?,?,?)";
    $sths{insert_foo_ora_sql} = $dbh->prepare ($sql);
    die(q{sth_prepare failed!}) unless $sths{insert_foo_ora_sql};
  }

  $rv = $sths{insert_foo_ora_sql}->execute(@arr);
  die(q{sth_execute failed:} . $!) unless $rv;

  return $arr[0]; # return foo_id
}

##################################################################
# Get foogroup using PL/SQL stored procedure
sub get_foo_ora_sp {
##################################################################
  my $foo_id = shift;

  if (!defined($sths{get_foo_sql_sp})) {
    my $prepstr = qq{BEGIN pkg_foo.proc_get_foo (:pi_foo_id, :pio_foo ); END;};
    $sths{get_foo_sql_sp} = $dbh->prepare($prepstr);
  }
  if (!defined($sths{get_foo_sql_sp})) {
    die(q{sth_prepare failed!});
  }

  my $retvalsth = undef;
  $sths{get_foo_sql_sp}->bind_param(q{:pi_foo_id}, $foo_id);
  $sths{get_foo_sql_sp}->bind_param_inout(":pio_foo", \$retvalsth, 0, { ora_type => ORA_RSET } );

  my $rv = $sths{get_foo_sql_sp}->execute();
  if (!$rv) {
    die(qq{sth_execute returned '$rv'});
  }

  my $number_of_rows = 0;
  $number_of_rows++ while ( my @row = $retvalsth->fetchrow_array );

  return $number_of_rows;
}


##################################################################
# Get foo using sql
sub get_foo_ora_sql {
##################################################################
  my $foo_id = shift;

  if (!$sths{get_foo_ora_sql_foo}) {
    my $sql = "SELECT foo_id,lnum1,lnum2,lnum3,lnum4 FROM foo WHERE foo_id = ?";
    $sths{get_foo_ora_sql_foo} = $dbh->prepare ($sql);
    die(q{sth_prepare failed!}) unless $sths{get_foo_ora_sql_foo};
  }

  my $rv = $sths{get_foo_ora_sql_foo}->execute($foo_id);
  die(q{sth_execute failed!}) unless $rv;

  my $number_of_rows = 0;
  $number_of_rows++ while (my @row = $sths{get_foo_ora_sql_foo}->fetchrow_array());

  return $number_of_rows;
}

##################################################################
sub update_foo_vars {
##################################################################
  foreach my $key (keys %foo) {
    $foo{$key}++;
  }
}

# ------ main
my $N = 1e2;
my $dbistr  = q{DBI:Oracle:};
my $dbiuser = q{scott};
my $dbipass = q{tiger};

GetOptions("N|n=i" => \$N) or pod2usage(2);

$dbh = DBI->connect($dbistr, $dbiuser, $dbipass);
die(q{connection failed!}) if (!defined($dbh));

timethese($N,
          {
           insert_and_select_foo_ora_sp => sub {
             update_foo_vars();
             my $foo_id = insert_foo_ora_sp(\%foo);
             get_foo_ora_sp($foo_id);
           },
           insert_and_select_foo_ora_sql => sub {
             update_foo_vars();
             my $foo_id = insert_foo_ora_sql(\%foo);
             get_foo_ora_sql($foo_id);
           }
          }
         );

__END__

##</code><code>##

-- pkg_foo.sql

CREATE OR REPLACE PACKAGE pkg_foo
AS
  FUNCTION func_insert_foo
  (
        lnum1         in  NUMBER,
        lnum2         in  NUMBER,
        lnum3         in  NUMBER,
        lnum4         in  NUMBER
  ) RETURN NUMBER;

  PROCEDURE proc_get_foo
  (
        pi_foo_id     in  NUMBER,
        pio_foo       out SYS_REFCURSOR
  );

END pkg_foo;
/

CREATE OR REPLACE PACKAGE BODY pkg_foo
AS

  FUNCTION func_insert_foo
  (
        lnum1         in  NUMBER,
        lnum2         in  NUMBER,
        lnum3         in  NUMBER,
        lnum4         in  NUMBER
  ) RETURN NUMBER AS
     my_foo_id           NUMBER;
   BEGIN

     SELECT FOO_ID_SEQ.nextval
       INTO my_foo_id
       FROM dual;

     INSERT INTO foo VALUES
     (
        my_foo_id,
        lnum1,
        lnum2,
        lnum3,
        lnum4
     );

     RETURN my_foo_id;

   END func_insert_foo;

   PROCEDURE proc_get_foo
   (
         pi_foo_id         in  NUMBER,
         pio_foo           out SYS_REFCURSOR
   ) IS
   BEGIN

    OPEN pio_foo FOR
       SELECT
        foo_id,
        lnum1,
        lnum2,
        lnum3,
        lnum4
       FROM foo
      WHERE foo_id = pi_foo_id;

  END proc_get_foo;

END pkg_foo;
/