#!/usr/bin/perl
#===============================================================================
#
#         FILE:  bind_param_array_file.pl
#
#        USAGE:  perl bind_param_array_file.pl
#
#  DESCRIPTION:  This script demonstrates how to use bind_param_array in DBI
#                when the data is being read from the file
#                It is a replacement of sqlldr used in conventional path mode
#       AUTHOR:  Parag Kalra, paragkalra@gmail.com
#      VERSION:  1.0
#      CREATED:  02-MAY-2010
# LAST CHANGED:  02-MAY-2010
# CHANGES MADE:  NONE
#===============================================================================

#Required modules
use strict;
use warnings;
use DBI;

# Declaring variables
my $uname="sa";
my $passwd="root123";
my $dbname="orcl";
my $row_count=0;
my (@product_code, @qty, @price, @order_date);

# Connecting to database
my $dbh = DBI->connect("dbi:Oracle:$dbname", $uname, $passwd,{
      PrintError => 0,
      RaiseError => 0
  } ) or die "Could not connect to the database\n";

# Storing the data into bind arrays
while(<>){
    chomp;
	($product_code[$row_count],$qty[$row_count],$price[$row_count],$order_date[$row_count])=split(/\t/);
	$row_count = $row_count+1;
}

my $drop_sql = "drop table PERL_SALES"; # Query to drop existing table
my $create_sql = "create table PERL_SALES (PRODUCT_CODE varchar(255), QTY NUMBER, PRICE FLOAT, ORDER_DATE date)"; # Query to create table
my $sth1 = $dbh->do(qq{ $drop_sql  }) ; #Droping table
my $sth2 = $dbh->do(qq{ $create_sql}) ; #Creating table

# Query to insert fetched records into SALES
my $INS = qq(insert into PERL_SALES(PRODUCT_CODE, QTY, PRICE, ORDER_DATE) values(?,?,?,?));
my $ins = $dbh->prepare($INS); #Preparing the query to insert records into child test table

# Using bind_param_array to execute large records using less inserts
$ins->bind_param_array( 1, \@product_code );
$ins->bind_param_array( 2, \@qty );
$ins->bind_param_array( 3, \@price );
$ins->bind_param_array( 4, \@order_date );
$ins->execute_array( { ArrayTupleStatus => \my @tuple_status } );

my $record_count = $row_count + 1;
print "\nTotal records processed: $record_count\n";

# This calculates the time of execution of code.
END {warn "\nTime of execution - ", time - $^T, " second(s)\n"}