#!perl -w

use DBI;

# output file 
$newfile = "./output.txt";

# open the new .txt file
open(BATCH, ">>$newfile") || die "Can't open $newfile : the output.txt file. $!";

# sets and prints the system date to the log file
$datestamp = `Date /T`;
print BATCH $datestamp;

# initialize the variable to count the batch lines created
$linecount = 0;

# variable with query to go against xyz.batch_lines table
$asql = "SELECT  batch_line  FROM xyz.batch_lines WHERE exe_c = 'N' ";
$bsql = "UPDATE cscadmin.crs_batch_lines SET exe_c = 'Y' WHERE exe_c = ? ";

# connect to oracle and extract data
my $dbh1 = DBI->connect("dbi:Oracle:CS001", "admin", "admin123");

my $statement = $dbh1->prepare($asql);
     $statement->execute; 
     $statement->bind_columns(\my ($batch_line, $row_id));

$statement->execute();
    while ($newfile= $statement->fetchrow_array) {
		$newfile=~s/p13/p12/g;
        	$newfile=~s/D:/\nD:/g;
		# $newfile=~s/\b5%\b/\b5%%\b/g;
		$newfile=~s/%/%%/g;
                      # write to the file
			$timestamp = `Time /T`;
			chop $timestamp;
               		print BATCH $newfile." ".$timestamp;
               # writes to screen
                print $newfile;   
               # counts the batch line being read
		$linecount++;    
	# executes the batch line
       # system($newfile);
        
# Mark batch lines in table as read    
       $statement2 = $dbh1->prepare($bsql);         
       $statement2 ->execute($row_id);
  
 } 

$statement->finish();
$statement2->finish();

# close oracle connection
$dbh1->disconnect();

# print count of batch lines
print BATCH $linecount;

# close new .txt file
close (BATCH);