dBforums - View Single Post - Need help on how to use Perl DBI script to poll logs from oracle databases?

spacebar · #2 (**permalink**) 08-03-10, 18:11

Quote:

However,the second wrapper script,call_dbipoll2.sh is successful only for the first run. For subsequent runs,I've noticed that countfile2 does not get updated with the latest timestamp(ie. the file returns to blank) and oracledb2.log returns to blank as well.

Since it works the first time and the files "/app/oracle/product/monitor/countfile2", "/opt/oracledb2.log" get populated with data that you viewed, then that means the 2nd run is not retrieving any rows via:

Code:

$sth = $dbh->prepare("SELECT $columns FROM $table where $countkey > \'$filecount\' order by $countkey");

and thus these files("/app/oracle/product/monitor/countfile2", "/opt/oracledb2.log") get removed/created empty since the script does not first validate data is retrieved before opening them with ">".

This is not really a problem but i would recommend changing the file test from

Code:

if ( -f $countfile)

to

Code:

if ( -s $countfile)

Now to see why data is not being returned, After the first run(successful) change the script to print the last value retrieved from $countfile by inserting a print:

Code:

    chomp $filecount;
    close (CF);
    print "Last entry 1st run: $filecount\n";

And also printing "$str" after it gets populated via:

Code:

  foreach my $key (keys(%$hash_ref)) {
    my $str = $hash_ref->{$key};
    print "str value: $str\n";

You can then see if the values retrieved are in the correct order.

After the above, You might also want to try it again adding sort to the keys function on the hash:

Code:

foreach my $key (sort(keys(%$hash_ref))) {

hth