Here's my latest attempt, but I keep getting the error:
ORA-01008 cannot bind variable or I would get
invalid column name
I thought maybe someone could look and have some input on this... where I might be at fault.. and I will still play around...
Thanks in advance, Zo.
#!perl -w
use strict;
use DBI;
# output file
my $newfile = "./test_output.txt";
open(BATCH, ">>$newfile") || die "Can't open $newfile : the test_outpu
+t.txt file. $!";
# sets and prints the system date to the log file
my $datestamp = `Date /T`;
print BATCH $datestamp;
# initialize the variable to count the batch lines created
my $linecount = 0;
# connection to database
my $dbh = DBI->connect("dbi:Oracle:CS1", "admin", "admin123");
my $sql = "SELECT batch_line, rowidtochar(rowid) FROM batch_lines";
my $sth = $dbh->prepare($sql);
$sth->execute();
my ($batch, $row_id);
$sth->bind_columns(\$batch, \$row_id);
my $sth2 = $sth;
my $count=1;
while($sth->fetch()) {
##### test print
# print BATCH "$row_id <$count>";
# print "$row_id <$batch>\n";
# $count++;
#####
$batch=~s/p12/servername/g;
$batch=~s/D:/\nD:/g;
$batch=~s/%/%%/g;
# write to the file
my $timestamp = `Time /T`;
chop $timestamp;
##### test print
print BATCH "$row_id <$count> $batch"." ".$timestamp;
#####
# print BATCH $batch." ".$timestamp;
# writes to screen
print $batch;
# counts the batch line being read
$linecount++;
##### test count
$count++;
#####
# executes the batch line
# system($batch);
##### update
print BATCH "\n^^^^^^^^^^^^^^$row_id^^^^^^^\n";
my $bsql = "UPDATE batch_lines SET exe_c = 'Y' WHERE exe_c = 'N
+' AND rowid = chartorowid($row_id) ";
$sth2= $dbh->prepare($bsql);
$sth2->execute();
#####
print BATCH "\n########### $row_id #########\n"
}
$sth->finish();
$sth2->finish();
# close oracle connection
$dbh->disconnect();
# print count of batch lines
print BATCH "\nLines extracted this period: ".$linecount."\n";
# close new .txt file
close (BATCH);