#!perl 

#########################################################
# This perl script will go to the oracle database, run a query, extract data and 
#        create a text (.txt) file with data information for a batch 
#        process to use.
#
#
#     October 4, 2001
#########################################################

use DBI;

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

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

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

# connect to oracle and extract data
my $dbh = DBI->connect("dbi:Oracle:Db001", "develop", "developer");
my $statement = $dbh->prepare($asql);
$statement ->execute();
    while ($newfile= $statement->fetchrow_array) {
               $newfile=~s/D:/\nD:/g;
                      # write to the file
               print BATCH $newfile;
               print $newfile;
     } 
$statement->finish();

# Mark batch lines in table as read          
my $statement2 = $dbh->prepare($bsql);
$statement2 ->execute();
$statement2->finish();

# close oracle connection
$dbh->disconnect();
$dbh2->disconnect();

# close new .txt file
close (BATCH);