RE: Problem populating execute using @array.

[Fox, Michael]

1. the prepare is missing, so $sth is not defined

2. the quoting loop is probably not doing what you want (its quoting the
tempoary variable field, not the elements of @ins) - but with placeholders
you probably don't want the fields quoted anyway


-----Original Message-----
From: Dolan, Mark [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 19, 2002 4:35 AM
To: Perl Users Help (E-mail)
Subject: Problem populating execute using @array.


I am trying to update a table in an Oracle database. I am reading an
input file which has 
the column names for the database as the first record in the file. The
data begins in the second row. I read the first record to pull off the
column names. I create the sql using placeholders. I then read the rest
of the file. At the end of the loop I attempt to perform the 'execute'
using the statement handle of the database. I get the following error.

DBD::Oracle::st execute failed: ORA-01722: invalid number (DBD ERROR:
OCIStmtExecute) at ./ins line 61, <$FH> line 1. 

At the time of the execute @ins = "20", "00002", "Hello, Dolly", "000",
"01", "022" 


# assume sth is a valid statement handle 
# assume $FH is a handle to an opened file 
my $columns = <$FH>; 
# 
# read first record of input file 
# split the column headers 
# 
  my $columnitem; 
  chomp($columns); 
  @ins = split /\|/, $columns; 
  foreach $columnitem (@ins) { 
   $columnitem = "\"".$columnitem."\""; 
  } 
my $formatcolumns=join ( ', ', @ins ); 
my $valstring= join ( ', ' , ('?') x scalar @ins ); 
my $sql= "INSERT INTO $table ( $formatcolumns ) VALUES ( $valstring) "; 
while ( my $line = <$FH> ) 
{ 
# 
# split the data fields 
# 
     chomp($line); 
     my $field; 
     @ins = split /\|/, $line; 
     foreach $field (@ins) { 
        $field = "\"".$field."\","; 
     } 
     chop($ins[-1]); # remove comma from last item in array 
     #the actual sequence 
     $sth->execute(@ins) or $err++; 
} 




Australia Post is committed to providing our customers with excellent service. If we 
can assist you in any way please either telephone 13 13 18 or visit our website 
www.auspost.com.au.

CAUTION

This e-mail and any files transmitted with it are privileged and confidential 
information intended for the use of the addressee. The confidentiality and/or 
privilege in this e-mail is not waived, lost or destroyed if it has been transmitted 
to you in error. If you have received this e-mail in error you must (a) not 
disseminate, copy or take any action in reliance on it; (b) please notify Australia 
Post immediately by return e-mail to the sender; and (c) please delete the original 
e-mail.