Re: Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk

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

Other related posts: