Denis,
> Thanks all. The caused is identified. The first subquery block in the WITH
> clause has a where condition: m_when between '14-Sep-2011' and
> '14-Nov-2011' , where m_when is a DATE type. In SQL Plus, this condition
> produces zero row, while SQL developer is smart enongh to translate the
> string to date type, which gives serveral hunderd k number of rows.
Ok, I'm slightly confused.
Are you saying that in the query yo have somethinbg like:
WHERE m_when between '14-Sep-2011' and ...
where you use strings as the "date" values you are looking for but that
Sql*Plus gives no rows while SQL Developer gives lots? That's surely a
bad thing.
Assuming your NLS_DATE_FORMAT string is something that your strinsg
resemble, Sql*Plus should be able to implicitly TO_DATE() the strings
and should return valid results?
If your date format is not compatible with your string format, Sql*Plus
should throw an error, not simply return zero rows?
Equally. SQL Developer should do the same - in my opinion, either
convert the date according to NLS_DATE_FORMAT or barf.
Just my £0.02.
Cheers,
Norm.
--
Norman Dunbar
Dunbar IT Consultants Ltd
Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL
Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l