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

From: Norman Dunbar <>
Date: Tue, 15 Nov 2011 14:35:19 +0000
Message-ID: <>


> 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.


Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
Received on Tue Nov 15 2011 - 08:35:19 CST

Original text of this message