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

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

While I'm happy you've narrowed down the problem, I think your cause is somewhat suspect as I don't believe the tools themselves are smart enough to do the translation you mention. That should be handled by the Oracle Client.

If SQL Plus and SQL Dev and Toad are all on the same client machine, are they all using the same ORACLE_HOME? If not, then I would think the issue is with client settings, e.g. NLS_DATE_FORMAT.

Someone pounce on me if I'm mistaken here...

Rich

--
http://www.freelists.org/webpage/oracle-l