#!/sbcimp/run/pd/perl/prod/bin/perl

# START OF MAIN
# ======================================================================
use strict;
use DBI;
use Getopt::Long;



# ====================================================================
# OPEN DATABASES
# ====================================================================


my $SERVER_NAME = 'smcmisd1';
my $uid = "cmis_extract/tara";

my $dbh1 = DBI->connect( "dbi:Oracle:$SERVER_NAME", "$uid", "", { AutoCommit =>0 }) || die $DBI::errstr;
#my $dbh2 = DBI->connect( "dbi:Oracle:$SERVER_NAME", "$uid", "", { AutoCommit =>0 }) || die $DBI::errstr;
my $dbh1_SERVER = $dbh1;
#my $dbh2_SERVER = $dbh2;

# ====================================================================
# APPLY LOGIC
# ====================================================================

	
    if(&extract_cm_trade_fact($dbh1_SERVER)) {
    	print "extract_cm_trade_fact worked.\n";
    }



# END OF MAIN
# =====================================================================
# SUBROUTINES

sub extract_cm_trade_fact
{
        my $local_db = $_[0];
        my (@result_set, @logdata);
        my $sql_cmd = "";


	my $sql_cmd1 = q{BEGIN sp_exec_imm('drop index CMBUS1'); END;};
	my $sql_cmd2 = q{select trade_date, host_cust_id from cm_trade_fact where trade_id = 210960459};

	my @sql_lines = ($sql_cmd1, $sql_cmd2);

	foreach $sql_cmd ( @sql_lines ) { 

	 print "$sql_cmd\n";
         @result_set = &execsql($local_db,$sql_cmd) ;
	 @logdata = split("~",$result_set[2]) ;

	 if ($result_set[2] != "") {
	  print "Return Status: $result_set[0]\n" ;
	  print "$logdata[0], $logdata[1]\n";
	 } else {
	  print "Return Status: $result_set[0]\n" ;
	 }

         undef @result_set ;
         $sql_cmd = "" ;

	}
}


sub execsql {

        my ($dbproc, $sqlstring) = @_;
        my (@temp_header, @header, @results, @data);

        my $sep = '~';
        my $count = 0;
        my $i = 0;
	my $rowcnt = 0;

	if ($sqlstring =~ /select/ || $sqlstring =~ /SELECT/) 
	{

	 ### If the SQL contains select statements do the following ###

         my $stmt_ptr1 = $dbproc->prepare($sqlstring) or die $dbproc->errstr;
         $stmt_ptr1->execute ();

         for ($i=1; $i <= $stmt_ptr1->{NUM_OF_FIELDS}; $i++)
         {
                push (@temp_header, $stmt_ptr1->{NAME}->[$i-1]);
         }

         @header = join($sep, @temp_header);
         while (@data = $stmt_ptr1->fetchrow_array ())
         {
                $count++;
                push (@results, join($sep, @data));
         }

         $stmt_ptr1->finish ();
         $rowcnt = $count;


	} else {

	 ### Otherwise, if the SQL contains only DML statements do the following ###

	 my $stmt_ptr2 = $dbproc->do($sqlstring) or die $dbproc->errstr;

	 my $row_status = $stmt_ptr2; 

#	 $stmt_ptr2->finish ();

	 $rowcnt = $row_status;
	 @header = ("");
	 @results = ("");


	}

        $dbproc->disconnect ();

        my @retvals = ($rowcnt, @header, @results);

} #END OF PROGRAM

1 ;