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

From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
Date: Tue, 15 Nov 2011 14:35:19 +0000
Message-ID: <4EC278A7.8040603_at_dunbar-it.co.uk>



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
Received on Tue Nov 15 2011 - 08:35:19 CST

Original text of this message