Hello Gurus,
I have a little problem when I bound three variables from bind_param.
I am using union in my sql. I am not sure where the problem comes arises.
The three fields from each table have different names although defined and
contain the same data (CHAR(10),VARCHAR2(40),CHAR(8)) I have TYPE=>SQL_CHAR.
Do I need to define separately for each variable I bind the value to its
type? If so how do I do that? If this is not the case, why not all of the
vars are getting bound?
I include a trace from the run and a piece of code.
Thanx a lot in advance for your help.
CODE:
#***************************************************************************
****
# prepared sqls
#***************************************************************************
****
my $sth_isid_frip =
$dbh->prepare(q{SELECT INST_MNEM,ID_CTXT_TYP,ISS_ID
FROM FT_T_ISID WHERE INSTR_ID=? AND
END_TMS IS NULL
UNION
SELECT
INST_MNEM,PREF_ID_CTXT_TYP,ISID_ISS_ID FROM FT_T_FRIP WHERE INSTR_ID=?
AND END_TMS IS NULL AND INST_MNEM
IS NOT NULL
ORDER BY INST_MNEM,ID_CTXT_TYP}) ||
die $dbh->errstr;
#***************************************************************************
***
my ($sql, %ids_hash, $instr_id);
$instr_id='0001x5MWFg';
#****************************************************************
# Creating a hash of ISID/FRIP ids
#****************************************************************
$sth_isid_frip->bind_param(1, $instr_id, {TYPE=>SQL_CHAR}); #
placeholders are numbered from 1
$sth_isid_frip->execute;
## the problem is somewhere here.
my( $prim_exch, $ctxt_typ, $id_value );
$sth_isid_frip->bind_columns( undef, \$prim_exch, \$ctxt_typ, \$id_value
);
#process further
while( $sth_isid_frip->fetch() ) {
if (defined $prim_exch and defined $id_value and defined
$ctxt_typ)
{ $ids_hash{$instr_id}{$prim_exch}{$ctxt_typ}=$id_value; }
} #while
$sth_isid_frip->finish();
TRACE:
DBI::db=HASH(0x116be8) trace level set to 2 in DBI 1.15-nothread
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x27ef08)~0x116be8 'SELECT
INST_MNEM,ID_CTXT_TYP,ISS_ID
FROM FT_T_ISID WHERE INSTR_ID=? AND
END_TMS IS NULL
UNION
SELECT
INST_MNEM,PREF_ID_CTXT_TYP,ISID_ISS_ID FROM FT_T_FRIP WHERE INST
R_ID=?
AND END_TMS IS NULL AND INST_MNEM
IS NOT NULL
ORDER BY INST_MNEM,ID_CTXT_TYP')
dbd_preparse scanned 2 distinct placeholders
fbh 1: 'INST_MNEM' NULLable, otype 96-> 5, dbsize 8/9, p8.s0
fbh 2: 'ID_CTXT_TYP' NULLable, otype 96-> 5, dbsize 8/9, p8.s0
fbh 3: 'ISS_ID' NULLable, otype 1-> 5, dbsize 40/41, p40.s0
<- prepare= DBI::st=HASH(0x26fb94) at get_isid_frip_ids.pl line 88.
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x26fb94)~0x1079cc 1
'0001x5MWFg' HASH(0x117a8c))
bind :p1 <== '0001x5MWFg' (type 1, attribs: HASH(0x117a8c))
bind :p1 <== '0001x5MWFg' (size 10/11/0, ptype 4, otype 96)
<- bind_param= 1 at get_isid_frip_ids.pl line 106.
-> execute for DBD::Oracle::st (DBI::st=HASH(0x26fb94)~0x1079cc)
dbd_st_execute SELECT (out0, lob0)...
!! ERROR: 1008 'ORA-01008: not all variables bound (DBD ERROR:
OCIStmtExecute)'
<- execute= undef at get_isid_frip_ids.pl line 107.
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x1079cc)~INNER)
<- DESTROY= undef.
-> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x116be8)~INNER)
<- DESTROY= undef.
Daniel Rozengurtel
Analyst II - Data Mining/WHSE
IT Clearing-Settlements
tel: (646) 733-4242
[EMAIL PROTECTED]
_____________________________________________________________________
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.
