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]

In reply to Re: Re: Placeholders, safety and the relative unimportance of efficiency by Zo
in thread SQL in Perl and setting variables by Zo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.