Hello again... OK.. it's been a long time since I've had to deal w/this issue, but now I have to finish up with it. I am going to post all the code from the script and then I will tell you what I need and can't figure out. I guess I'm what one might call a "code-rock"... just can't penetrate my brain w/the information!
I'm running on WindowsNT server w/Perl v.5.06... The script runs now, but I have to make it more specific to the data that is pulling in, more unique to the lines from the database. Again I have the Perl Cookbook and Perl in a Nutshell and have gone online to all sights figuring this out, and I humbley come here...

I connect no problem, extract what I need, then update. This all works. Now I need to select in 2 fields, not one.
I am selecting a field where exe_c = 'N'. Now I also need to select were exe_c = 'N' and rowid = ? (unknown). The rowid is the identifier generated in Oracle that I don't know, but is unique to the row that I'm pulling in. After the row is selected and worked on in the loop, I would like to update exe_c from 'N' to 'Y' where exe_c = 'n' and the rowid = ?.

$asql = "SELECT batch_line, rowid FROM xyz.batch_lines WHERE exe_c = 'N' ";

but how would I still do the work to the batch_line that I'm taking in AND take in the rowid as a variable (which is unknown) then use it (or bind it, which I tried but can't figure out) to the UPDATE statement?

$bsql = "UPDATE xyz.batch_lines SET exe_c = 'Y' WHERE exe_c = 'N' AND row_id = ? ";

The following is the script that I am currently running. Thank you for all the help in advance.

use DBI;
use strict;

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

# open the new .txt file
open(BATCH, ">>$newfile") || die "Can't open $newfile : the batch_line
+_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;

$asql = "SELECT  batch_line  FROM xyz.batch_lines WHERE exe_c = 'N' ";
$bsql = "UPDATE xyz.batch_lines SET exe_c = 'Y' WHERE exe_c = 'N' ";

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

my $statement = $dbh1->prepare($asql);
$statement ->execute();
    while ($newfile= $statement->fetchrow_array) {
        $newfile=~s/p13/p12/g;
            $newfile=~s/D:/\nD:/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();
   } 

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

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

# print count of batch lines
print BATCH $linecount;

# close new .txt file
close (BATCH);
[download]

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.