Maybe you can show us the SQL command(s) and examples of where/how the results differ between Java and TOAD and Perl.

Note that both, ODBC and JDBC have ways of tracing their commands so maybe you can find a difference there.

Are you using DBD::ODBC or DBD::Oracle to connect to your database?

Yes sure. This is my function in Perl script:

sub getDTfromDBnew {
    bopc_log(":::::::::::::::::::::::::::::::::::::::::::::::::::");
    bopc_log ("Estraggo lista DA da DB (query NUOVA)");
    my $user = "user";
    my $password = "password";

    my $dbh = DBI->connect("dbi:Oracle://<ALIAS>:1531/<SID>",$user, $p
+assword, { RaiseError => 1, AutoCommit => 0 });

 my $sql  ="select ID, DT_NAME
 from
 (
 SELECT
       d.ID,
       d.DT_NAME,
       s.SCHEDULE_TYPE,
       s.start_date, s.end_date, s.duration, s.start_time, s.selected_
+days
       ,CAST((FROM_TZ(CAST((START_DATE / (60*60*24*1000)) + to_date('0
+1011970','DDMMYYYY') AS TIMESTAMP),'+00:00') AT TIME ZONE SCHEDULE_TI
+MEZONE) AS DATE) START_DATE_CONV
       ,CAST((FROM_TZ(CAST((END_DATE / (60*60*24*1000)) + to_date('010
+11970','DDMMYYYY') AS TIMESTAMP),'+00:00') AT TIME ZONE SCHEDULE_TIME
+ZONE) AS DATE) END_DATE_CONV
       ,(sysdate) TS_NOW
       ,( To_number(To_char(SYSDATE, 'HH24')) * 3600 + To_number(To_ch
+ar(SYSDATE, 'MI')) * 60 )+(To_number(Decode( To_char(SYSDATE, 'D'), 7
+, 0,  To_char(SYSDATE, 'D')))*86400) TSX
       ,((to_number(to_char(sysdate, 'HH24'))*3600 + to_number(to_char
+(sysdate, 'MI'))*60) + (extract(DAY FROM sysdate)*86400)) TS_MONTH
 FROM BSMMOM_PROD_MGMT.DT_DOWNTIME d inner join BSMMOM_PROD_MGMT.DT_SC
+HEDULE s on d.SCHEDULE_ID = s.ID
 )
 where
 (
            (
            SCHEDULE_TYPE = 1
                and TS_NOW >= START_DATE_CONV and TS_NOW < END_DATE_CO
+NV
            )
        or
            (
            SCHEDULE_TYPE = 2 and TS_NOW >= START_DATE_CONV and ((END_
+DATE = 0) or (END_DATE <> 0 and TS_NOW < END_DATE_CONV))
                and
                    (
                         (   ((0*86400+start_time)<TSX) AND ( ( (0*864
+00+start_time+duration)<(7*86400)  AND  (0*86400+start_time+duration)
+ > TSX ) OR (  (0*86400+start_time+duration)>=(7*86400)  AND  ((0*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 0)) > 0 )
                         OR
                         (   ((1*86400+start_time)<TSX) AND ( ( (1*864
+00+start_time+duration)<(7*86400)  AND  (1*86400+start_time+duration)
+ > TSX ) OR (  (1*86400+start_time+duration)>=(7*86400)  AND  ((1*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 1)) > 0 )
                         OR
                         (   ((2*86400+start_time)<TSX) AND ( ( (2*864
+00+start_time+duration)<(7*86400)  AND  (2*86400+start_time+duration)
+ > TSX ) OR (  (2*86400+start_time+duration)>=(7*86400)  AND  ((2*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 2)) > 0 )
                         OR
                         (   ((3*86400+start_time)<TSX) AND ( ( (3*864
+00+start_time+duration)<(7*86400)  AND  (3*86400+start_time+duration)
+ > TSX ) OR (  (3*86400+start_time+duration)>=(7*86400)  AND  ((3*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 3)) > 0 )
                         OR
                         (   ((4*86400+start_time)<TSX) AND ( ( (4*864
+00+start_time+duration)<(7*86400)  AND  (4*86400+start_time+duration)
+ > TSX ) OR (  (4*86400+start_time+duration)>=(7*86400)  AND  ((4*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 4)) > 0 )
                         OR
                         (   ((5*86400+start_time)<TSX) AND ( ( (5*864
+00+start_time+duration)<(7*86400)  AND  (5*86400+start_time+duration)
+ > TSX ) OR (  (5*86400+start_time+duration)>=(7*86400)  AND  ((5*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 5)) > 0 )
                         OR
                         (   ((6*86400+start_time)<TSX) AND ( ( (6*864
+00+start_time+duration)<(7*86400)  AND  (6*86400+start_time+duration)
+ > TSX ) OR (  (6*86400+start_time+duration)>=(7*86400)  AND  ((6*864
+00+start_time+duration)-(7*86400)) > TSX   ) ) AND Bitand(selected_da
+ys, Power(2, 6)) > 0 )
                      )
          )
        or
            (
            SCHEDULE_TYPE = 3 and TS_NOW >= START_DATE_CONV and ((END_
+DATE = 0) or (END_DATE <> 0 and TS_NOW < END_DATE_CONV))
                and
                    (
                        (
                              ((SELECTED_DAYS * 86400 + START_TIME) <=
+ TS_MONTH) AND ((SELECTED_DAYS * 86400 + START_TIME + DURATION) > TS_
+MONTH)
                          )
                      )
          )
 )";

    #print "$sql\n";

    my $sth = $dbh->prepare($sql);
    my $counter=0;
    $sth->execute();
    while (my @row = $sth->fetchrow_array) {
       #print "fname: $row[0]  lname: $row[1]\n";
       $dtFromDB{lc($row[0])}= $row[1];
    }

    bopc_log(":::::::::::::::::::::::::::::::::::::::::::::::::::");
    foreach my $key (keys %dtFromDB) {
            #print "DB --> KEY: $key - VALUE: $dtFromDB{$key}\n";

             bopc_log ("            DB DT --> KEY: $key - VALUE: $dtFr
+omDB{$key}\n");
            #bopc_log ("DT $counter: $list[0]:$list[2]");
     }

    my $hashKeysNum=scalar keys %dtFromDB;
    #print "HASH HAS # KEYS: $hashKeysNum\n";
    bopc_log(":::::::::::::::::::::::::::::::::::::::::::::::::::");
    bopc_log("DB QUERY NUOVA riporta $hashKeysNum DT Attivi");


    $dbh->disconnect;
[download]

Consider that if i run this query manually through SQL developer client i got different output, in particular records number does not match with script parameter $hashKeysNum. If i compare output between Perl ($hashKeysNum variable) and TOAD, they MATCH. Thanks. Regards, Fabrizio