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]

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?

Thanks for the answer. I have run the query selecting  count(*) and the result is the same as retrieval but different from SQL developer client. The correct result seems Java one, TOAD+PERL are wrong. Regards, Fabrizio