But this issue is extremely important. You will certainly want to be able to handle more realistic values like "O'Connor" and "Smith's Bakery". If you let DBI handle the quoting for you, you will save yourself a lot of trouble fixing annoying bugs.

buckaduck

Some people will probably naively ignore this advice because they think that it's unlikely that their program will ever use a value of just a single quote...

You can always tell the inexperienced programmers. They say things like "Oh, that'll never happen."

xoxo,
Andy
--
<megaphone> Throw down the gun and tiara and come out of the float! </megaphone>

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]

I tried to put in the bind_column, but still not working... or I'm getting an error:
Bind columns called with 2 refs when 1 needed.
thanks again for any help!

#!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);
[download]

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);
[download]

Hello..
Just a 'thanks' to everyon for all the help with this. I've learned much. I have a working query now.
brother monk Zo.