#!/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/###########";

my $dbh = DBI->connect( "dbi:Oracle:$SERVER_NAME", "$uid", '', { AutoCommit => 0 }) || die $DBI::errstr;
my $dbh_SERVER = $dbh;

$dbh_SERVER->func('dbms_output_enable');
print "AUTO_COMMIT is set to: $dbh_SERVER->{AutoCommit}\n";  

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

my $eval_status = "";
my $rollback_status = "";
	
&extract_cm_trade_fact($dbh_SERVER);

if($eval_status	=~ /Database Execution Error/) { 
        eval { $rollback_status = $dbh_SERVER->rollback(); };
       	print("Rollback Status: $rollback_status\n");
	print "\n$eval_status\nROLLING BACK TRANSACTION...\n";
	goto EXITCLEAN;
} elsif($eval_status =~ /Eval Error During Execution/) {
	print "\nWARNING: $eval_status\n";
	goto EXITCLEAN;
} else {
	print "\nSQL Statements Processed Successfully.\n";
}

EXITCLEAN: print "Disconnected from Database and Terminating.\n";
	   $dbh->disconnect ();


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

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


	my $sql_cmd1 = q{create table t_dave_test as select trade_date, host_cust_id from cm_trade_fact where trade_id = 210960459_compile_};
	my $sql_cmd2 = q{delete from t_dave_test where trade_id = 'WA339482'_compile_};

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

	
   eval {
	
	$local_db->{RaiseError} = 1;  # Enable the RaiseError DBH attribute

	foreach $sql_cmd ( @sql_lines ) { 

	 print "AUTO_COMMIT is set to: $local_db->{AutoCommit}\n";  
	 print "$sql_cmd\n";
         @result_set = &execsql($local_db,$sql_cmd) ;

	 if ($result_set[2] =~ /~/) {
	  print "This SQL Statement was Processed Successfully.\n";
	  print "Return Status: $result_set[0] Rows Effected\n" ;
	  @logdata = split("~",$result_set[2]) ;
	  print "$logdata[0], $logdata[1]\n";
	 } else {
	  print "This SQL Statement was Processed Successfully.\n";
	  print "Return Status: $result_set[0] (Rows Effected or Error Code if function or procedure)\n" ;
	  if ($sql_cmd !~ /drop/ && $sql_cmd !~ /DROP/) {
	    print "$result_set[2]\n";
	  }
	 }

         undef @result_set ;
         $sql_cmd = "" ;

	} #FOREACH

	print "Committing transaction...\n";
#	$local_db->commit();

    }; #EVAL

	if ($@ =~ /Ora/) {
		print "This SQL Statement Produced an Error...\n";
        	$eval_status .= "Database Execution Error: $@";
   	} elsif ($@) {
		print "There was a Program Error During While Processing this SQL Statement...\n";
        	$eval_status .= "Eval Error During Execution: $@";
   	} 

}


sub execsql {

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

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


	if ($sqlstring =~ /_compile_select_/ ) 
	{

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

	 $sqlstring =~ s/_compile_select_//g;
         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 ###
	 $sqlstring =~ s/_compile_//g;

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

	 my $row_status = $stmt_ptr2; 
	 my $output_statement = $dbproc->func('dbms_output_get');

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

	}


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


} #END OF PROGRAM


1 ;