This content has been marked as final. Show 6 replies
some possible reasons (I suppose kt_stamm_20072_v is a view):
a) kt_stamm_20072_v reads data from a global temporary table, which holds different data for different sessions
b) kt_stamm_20072_v reads data from a materialized view
c) you connect with different users
d) SQL*Developer sometimes shows strange behaviour if you omit the semicolon at the end of a statement
e) your view has where-clauses which are time dependent
Thanks for your reply.
some possible reasons (I suppose kt_stamm_20072_v isSorry, I forgot to say.
a) kt_stamm_20072_v reads data from a globalNot that I can see it. It seems to read from a number of other tables and views.
temporary table, which holds different data for
We changed it to read from only tables (the views were not really necessary) but the effect is still the same.
b) kt_stamm_20072_v reads data from a materializedI don't think so.
c) you connect with different usersAlways the same user.
d) SQL*Developer sometimes shows strange behaviour ifI always put the semicolon. Also it seems to be an SQL*Developer-Golden-SQuirreL-versus-SQL*Plus-Toad-problem.
you omit the semicolon at the end of a statement
e) your view has where-clauses which are timeThey shouldn't and the data is changing very slowly. Also there doesn't seem to be a time dependency but a tool dependency. All sessions were newly started. The effect was seen yesterday the first time (before that no one checked so it might have been there) and is still there today. I rebooted my computer since yesterday.
Any further suggestions?
I would use
EXPLAIN PLAN FOR select *from kt_stamm_20072_v where ik = '100602360';
select * from table(dbms_xplan.display);
Then look at the filter and access predicates.
There may be something based on a SYS_CONTEXT (row-level security or similar), or package variable.
Another possibility is some sort of implicit date conversion which may give different results for different NLS_DATE_FORMATs (eg 010199 would match a 1999 date with an RR format mask, but a 2099 year with YY).
as Gary suggested, you can check if your NLS formats in Toad/SQL*Plus and SQL developer are the same. You can see this e.g. by
select * from nls_session_parameters;
If the settings are different, you can use alter session commands to make them equal. Then run your query again and see if the results are still different.