PLSQL Developer answers "0 rows returned" while Excel runs statement fine.

[Aircooled]

I am having a problem with PLSQL Developer version 8.0 and version 9.0.

I am running on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Windows XP 2002 Professional SP3

All other queries run smooth on PLSQL except for the Oracle "with" (subquery factoring), used to fill in the blanks in a query and get rows returned (with zero) where there is no data. Other timestamps just return the amount of transactions at that time. If I do not use "with" I only get the records where there are transactions.

The query below returns the amount of transactions for each second, and (thanks to the "with") it returns 0 for the seconds with no transactions.

------------------------------------------------------
with dt_range as (
select to_date ('010512 14:21:00', 'ddmmyy HH24:mi:ss') as start_dt,
to_date ('020512 21:59:59', 'ddmmyy HH24:mi:ss') as end_dt
from dual
),
all_seconds as (
select start_dt + interval '1' second * level as second_counter
from dt_range
connect by level < (select (end_dt - start_dt) * 24 * 60 * 60 from dt_range)
)
select to_char(second_counter, 'dd-mon-yy - hh24:mi'),
count(mt.upd_tms)
from all_seconds s
left join ama.FLIGHT_SEGLEGTB mt on to_char(mt.upd_tms,'yyyy-mm-dd hh24.mi.ss') = to_char(s.second_counter,'yyyy-mm-dd hh24.mi.ss')
where s.second_counter between (select start_dt from dt_range) and (select end_dt from dt_range)
group by to_char(second_counter, 'dd-mon-yy - hh24:mi')
order by to_char(second_counter, 'dd-mon-yy - hh24:mi')

-----------------------------------------------------

What actually happens is that PLSQL Developer answers "0 rows returned". I am puzzled because the same statement with the same db with the same user logged in in a colleagues PLSQL Developer returns over 1000 rows. When I log in with Excel in my PC, Oracle returns also over 1000 rows nice and dandy.

So there is a setting in my PLSQL Developer inhibiting the normal execution of my "with" (subquery factoring) query. I've been using PLSQL Developer intensively and have not experienced any kind of problems with execution of statements. It just does the job.

Would appreciate any kind of tips. I do not want to replace "with" I just wish to know why it answers "0 rows returned" in PLSQL Developer.

thanks in advance

 

[Theod]

Hi,

I'm not an DBA, so this answer is not technical correct, use this only as hint.
But for me those problem did appear some years ago, as I remember, with wrong Oracle-Net-Versions at my client. My DBA did solve this.

 

[Aircooled]

Thanks for your reply.

I found in another forum a similar problem from another user, except it was using TOAD as data base client.

The explanation was that statements NOT starting with "select" were treated wrongly and answered "0 rows returned". This is the same problem I experience. Furthermore, "0 rows returned" implicitly means there were no ORA- errors ocurring.

What puzzles me is that another users at my office using PLSQL Developer can run the statement without problems.

The above TOAD scenario points toward the right direction regarding my issue since I can run my statmenet in PLSQL developer by surrounding the subquery factoring statement with "select * from":

----------------------------------------------------------

select * from (
with dt_range as (
select to_date ('090512 00:21:00', 'ddmmyy HH24:mi:ss') as start_dt,
to_date ('090512 10:59:59', 'ddmmyy HH24:mi:ss') as end_dt
from dual
),
all_seconds as (
select start_dt + interval '1' second * level as second_counter
from dt_range
connect by level < (select (end_dt - start_dt) * 24 * 60 * 60 from dt_range)
)
select to_char(second_counter, 'dd-mon-yy - hh24:mi'),
count(mt.upd_tms)
from all_seconds s
left join ama.FLIGHT_SEGLEGTB mt on to_char(mt.upd_tms,'ddmmyy HH24:mi:ss') = to_char(s.second_counter,'ddmmyy HH24:mi:ss')
where s.second_counter between (select start_dt from dt_range) and (select end_dt from dt_range)
and (select * from ama.SCHEDULE_CODESHARETB s not in ('SK','WF','KF')
group by to_char(second_counter, 'dd-mon-yy - hh24:mi')
order by 1);

----------------------------------------------------------

obtaining this way the right answer. Very weird indeed. I still suspect this is a settings issue in PLSQL Developer.