Dear Gurus,
I am having problems with updating my Oracle table when changing one table's
column data type from VARCHAR2 to CHAR.
This particular column is in WHERE statement for an update. If it was left
as VARCHAR2 I would have no problems. However I need this data type be
persistent so that I can join on it different tables.
As far as my code goes (I might be wrong though) the values I am fetching
with which I am to update the table are bound to SQL_CHAR dataset. I am not
sure how to fix/understand that problem. As I said the minute I change my
column from CHAR back to VARCHAR2 I am golden. Could you please advise what
I am doing wrong and whether I am forgetting anything in my code. Here is a
snapshot of what I am doing.
Thanx much for your help and attention
Daniel
PS: DBI version 1.20
Perl 5.6.1
Oracle 8.1
# prepare stmt for later to fetch from ISST
$sql = "
SELECT ".join( ", ", @isst_cols )." FROM FT_T_ISST WHERE INSTR_ID=?
AND INST_MNEM IS NOT NULL
AND END_TMS IS NULL
ORDER BY LAST_CHG_TMS DESC";
my $sth_isst=$dbh->prepare_cached($sql) || die $dbh->errstr;
# prepare stmt to update CORPS
$sql="UPDATE CORPS_$weekday SET ";
foreach (@IsstArray) {$sql.="$_ = ?, ";}
$sql.=" LAST_CHG_TMS=SYSDATE WHERE INSTR_ID = ? AND INST_MNEM = ?";
my $sth_updateIsst = $dbh->prepare($sql) or die $dbh->errstr;
# fetch distinct instr_id from corps so that I can update that/these entries
with
# data obtained from ISST.
my $sth_corps=$dbh->prepare_cached("SELECT DISTINCT INSTR_ID FROM
CORPS_$weekday") || die $dbh->errstr;
undef $instr_id;
$sth_corps->execute;
$sth_corps->bind_columns( \$instr_id);
#big loop within which I update my table.
while( $sth_corps->fetch() ){
undef %row;
$sth_isst->bind_param(1, $instr_id, {TYPE=>SQL_CHAR});
$sth_isst->execute;
$sth_isst->bind_columns( \( @[EMAIL PROTECTED] ));
while( $sth_isst->fetch() ){
# ...
# Obtaining values from fetch to update my table later
# ...
}
# Update CORPS table after finished looping on ISST table
eval { $sth_updateIsst->execute(@a1, $instr_id, $inst_mnem);};
if ($@) { $log->write("ERROR","Transaction aborted for $instr_id
because $@"); }
}
____________________________________________________________________
IMPORTANT NOTICES:
This message is intended only for the addressee. Please notify the
sender by e-mail if you are not the intended recipient. If you are not the
intended recipient, you may not copy, disclose, or distribute this message
or its contents to any other person and any such actions may be unlawful.
Banc of America Securities LLC("BAS") does not accept time
sensitive, action-oriented messages or transaction orders, including orders
to purchase or sell securities, via e-mail.
BAS reserves the right to monitor and review the content of all
messages sent to or from this e-mail address. Messages sent to or from this
e-mail address may be stored on the BAS e-mail system.
__________________________________________________________________
