Ok, hello all again. Since last posting, I have not made much progree.
Thanks Tim for responding.....
I did a trace on what is happening (as suggested by Tim) within the call
to the database. I have shown the output below.
My data structure:
Inserting into db: cardTypePollID = 20
cardType => $VAR1 = {
'compID' => '17',
'asnNumber' => '.1.3.6.1.4.1.9.3.6.11.1.2',
'results' => {
'1' => 'cpu-2600',
'2' => 'wic-s-t-2186',
'3' => 'pm-16a'
}
};
My Perl code...then output:
my $plsql_params = "'" . join("', '", $ASNNameHash->{compID},
$pollID, $answer, $asnNum) . "'" ;
my $sql = qq{BEGIN
DEVICE_MANAGEMENT.ADD_COMPONENTDETAILS($plsql_params); END;};
print qq(\n*$sql*\n) if $debug;
$sth = $$dbh->prepare($sql); # End of the prepare
$sth->execute();
$PassFail = ( ! ( defined ($sth->err) ) ) ? 'PASS' : 'FAIL';
write OUT_DETAIL if $debug;
}
OUTPUT:
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x359190)~0x32f3f4
'BEGIN DEVICE_MANAGEMENT.ADD_COMPONENTDETAILS('17', '20', 'cpu-2600',
'.1.3.6.1.4.1.9.3.6.11.1.2.1'); END;')
dbih_setup_handle(DBI::st=HASH(0x4a5088)=>DBI::st=HASH(0x4a448c),
DBD::Oracle::st, 4a4510, Null!)
dbih_make_com(DBI::db=HASH(0x32f3f4), DBD::Oracle::st, 208)
dbd_st_prepare'd sql BEGIN
dbd_describe skipped for BEGIN
<- prepare= DBI::st=HASH(0x4a5088) at OraMod.pm line 415 via
./NetAssetMan.pl line 105
-> execute for DBD::Oracle::st (DBI::st=HASH(0x4a5088)~0x4a448c)
dbd_st_execute BEGIN (out0, lob0)...
dbd_st_execute BEGIN returned (SUCCESS, rpc1, fn34, out0)
<- execute= 1 at OraMod.pm line 416 via ./NetAssetMan.pl line 105
-> err for DBD::Oracle::st (DBI::st=HASH(0x4a5088)~0x4a448c)
<- err= undef at OraMod.pm line 417 via ./NetAssetMan.pl line 105
No update occurs even though it returns a success from the BEGIN...
Then, I cut-n-paste from the above output...the BEGIN....END; stuff....
i.e. BEGIN DEVICE_MANAGEMENT.ADD_COMPONENTDETAILS('17', '20',
'cpu-2600', '.1.3.6.1.4.1.9.3.6.11.1.2.1'); END;
and viola, it works. So my original impression that is could be with the
PL/SQL is no longer true; well sortof, read on. The PL/SQL seems to work
fine, only not when I call it from perl. Could it be that I am running
an 'old' version of DBD::Oracle ??? I am running 1.12 and I think the
latest is 1.14. Oracle Version 9.2.0.1, sun solaris 8 and perl 5.6.1
built for sun-4 solaris. Here is my PL/SQL that I thought was dodgy
(marked with ->)....
OPEN check_ComponentDetails(p_ComponentID, p_ASNNumber);
FETCH check_ComponentDetails INTO v_answer;
IF check_ComponentDetails%NOTFOUND THEN
INSERT INTO ComponentDetails
(yadda,yadda,......);
-> ELSIF (v_answer != p_Answer) THEN
-> UPDATE ComponentDetails
-> SET answer = p_answer,
-> PollingID = p_PollingID
-> WHERE ComponentID = p_ComponentID
-> AND ASNNumber = p_ASNNumber;
-> END IF;
Funny thing here though. If I squeeze a floating point number thru the
comparison above (v_answer != p_answer) then it catches the difference
every time. It only does not seem to work when injecting strings and....
then only from perl.
Funny one. Any help gladly accepted....
Cheers
H
