sqldeveloper outputs correct result for count(*) while sqlplus results zero

Question

I am getting different output for count(*) in sqlplus and in sqldeveloper. The Database is Oracle 11G.

SQL Developer

Select count(*) from table1 where web_ck_d = to_char(sysdate-1,'DD-MON-YY');
Count(*)
1083171

Sqlplus

SQL> Select count(*) from table1 where web_ck_d = to_char(sysdate-1,'DD-MON-YY');
COUNT(*)
      0

The table has NUM_ROWS-357238869, BLOCKS-14978755, PARTITIONED-YES in all_tables.

Not understanding why sqlplus is resulting 0. Please help.

All the changes are committed. I am checking this last 3 days. For other tables there is no issue and outputting correct result in both sqlplus and sqldeveloper. — stormfield
– stormfield, Commented Sep 15, 2014 at 7:01
You might be referring a wrong table, check the synonyms and also try schemaname.tablename. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Sep 15, 2014 at 7:15

Darshan Lila · Accepted Answer · 2014-09-15 05:48:36Z

2

When you use custom date formats, and if oracle does not find it suitable, it will go and look for default date formats in sessions.

Sql-developer has its own session and sqlplus has its own. Sql developer's date format is specified in NLS date parameter and you can check it from : Tools > Preferences > Database > NLS.

You can check the date in sql plus using following query

select sysdate from dual;

And you can set the desired date format as follows:

alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

You can use your desired format here.

answered Sep 15, 2014 at 5:48

Darshan Lila

5,8762 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqldeveloper outputs correct result for count(*) while sqlplus results zero

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related