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

Note that if d.ID is not unique, you will throw data away.

I would compare the number of rows by doing (another) subselect, selecting count(*), just to see whether it is the query or the retrieval that generates the different number of rows.

Also, try reducing the SQL to the minimum SQL needed to reproduce the problem between Java and Perl+TOAD.

Have you manually verified whether TOAD+Perl or Java do return the correct result?