Currently, i am passing two keymodule entries as input and would like to change process by passing all the keycatmodule_id whatever present in the database.. please let me know how to change the script.
our @keymodule_id = (8401, 8404);
sub sane {
my $self = shift;
my %lookup;
my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
week_first_mv, week_last_mv
from us_item_tbl i
where keymodule_id = ? });
for my $key ( @keymodule_id) {
$sth->execute ($key);
$sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
while ($sth->fetch) {
my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
$upc = sprintf "%014s", $cpu;
$lookup{$nae}{$cpu} = [$wad, $wlm];
}
}
$dbh->disconnect;
$$self{'SANE'} = \%lookup;
}
I have tried the below and getting the memory error.
#our @keymodule_id = (8401, 8404); #commented this line
sub sane {
my $self = shift;
my %lookup;
my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
week_first_mv, week_last_mv
from us_item_tbl i
where keymodule_id = ? });
$sth->execute ();
$sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
while ($sth->fetch) {
my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
$upc = sprintf "%014s", $cpu;
$lookup{$nae}{$cpu} = [$wad, $wlm];
}
Please confirm whether the abiove is correct.
@nmkl2021 ,
please, show ACTUAL workings - not commentary , it's much more helpful if the team can see actual errors/messages produced from user code.
if you are going to be passing 1 or more 'keymodule_id' then change the
where keymodule_id = ?
to
where keymodule_id in (...)
see https://www.oradev.com/oracle_sql_in_clause.html for syntax and example.
you have not clearly stated how that list is going to be constructed, so please clarify
if its from a file then there are plenty of online perl examples on how to achieve that.
NB: showing data expected to be processed is also vital for the team in order to avoid misunderstanding/confusion.
I have tried using where keymodule_id in (...) and getting a syntax error below.
DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD ERROR: error possibly near <*> indicator at char 255 in
My intension is currently we are passing only below three values.
our @keymodule_id = (8401, 8404);
I am expecting the keycatmodule_id to be pulled through the oracle query and pass all the values present in the table.
select distinct(keycatmodule_id) from us_item_tbl;
Currently three values are passed manually instead i should process all the keycat values.
I have tried the below queries and got the keycatmodule in the array.
Currently, not sure how to pass this array in the sub sane function.
sub keymodule {
my $self = shift;
my %lookup;
my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
my $sth = $dbh->prepare (qq{ select distinct keymodule_id
from us_item_tbl });
$sth->execute;
my $arrayref = $sth->fetchall_arrayref();
foreach (@$arrayref) {
print "$_\n";
}
$dbh->disconnect;
}
Output
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff980)
ARRAY(0x407ff98
@nmkl2021 , try searching for 'array passing in perl' via google (or search engine of your choice) - there are many examples. here's one for starters https://www.perltutorial.org/passing-array-references-to-subroutines/
I have tried the below command and getting the out of memory issues. I am not sure how to clear the cache to get rid of the out of the memory issue.
sub sane {
my $self = shift;
my %lookup;
my $dbh = DBI->connect ('dbi:Oracle:usbmfs', 'US', 'states', {AutoCommit => 0, RaiseError => 1});
my $sth = $dbh->prepare (qq{ select cpu_id, system, gen, vendor, item,
week_first_moved, week_last_moved
from us_item_tbl });
$sth->execute ();
$sth->bind_columns (\(my( $cpu, $sys, $gen, $vend, $item, $wad, $wlm)));
my $rows = $sth->fetchall_arrayref({});;
for my $row (@$rows) {
my $nae = sprintf "%02s%05s%05s", $sys, $vend, $item;
print "$nae\n";
$upc = sprintf "%014s", $cpu;
$lookup{$ean}{$cpu} = [$wad, $wlm];
}
# }
$dbh->disconnect;
$$self{'SANE'} = \%lookup;
}
@nmkl2021 , hi,
can you PLEASE always supply actual output generated when executing any code that is returning errors.
testing/tuning/testing is your friend !
you are almost there , hang in there !!!