This discussion is archived
6 Replies Latest reply: Jun 3, 2008 11:55 PM by 589830 RSS

Incorrect results returned by SQL Developer

589830 Newbie
Currently Being Moderated
Hi,

Using a simple
select *
from kt_stamm_20072_v
where ik = '100602360'
results in one row returned.
SQL*Plus returns two rows. So does Toad. Sorry, no versions known.
SQuirreL and Golden (version: 5.7 Build: 447) both return only one row.
This is a severe problem because I don't have access to Toad and I'm not to keen on using SQL*Plus since I don't know it.
I'm using
Java Platform 1.5.0_14
Oracle IDE 1.2.1.3213

Can anyone help? I really think one should be able to rely on an SQL-client.

Thanks

Franziska
  • 1. Re: Incorrect results returned by SQL Developer
    608712 Newbie
    Currently Being Moderated
    Hello Franziska,

    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

    Regards, Dieter
  • 2. Re: Incorrect results returned by SQL Developer
    -K- Guru
    Currently Being Moderated
    Sure you committed/rolled back in all programs?

    K.
  • 3. Re: Incorrect results returned by SQL Developer
    589830 Newbie
    Currently Being Moderated
    Hi Dieter

    Thanks for your reply.
    some possible reasons (I suppose kt_stamm_20072_v is
    a view):
    Sorry, I forgot to say.
    a) kt_stamm_20072_v reads data from a global
    temporary table, which holds different data for
    different sessions
    Not that I can see it. It seems to read from a number of other tables and views.
    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 materialized
    view
    I don't think so.
    c) you connect with different users
    Always the same user.
    d) SQL*Developer sometimes shows strange behaviour if
    you omit the semicolon at the end of a statement
    I always put the semicolon. Also it seems to be an SQL*Developer-Golden-SQuirreL-versus-SQL*Plus-Toad-problem.
    e) your view has where-clauses which are time
    dependent
    They 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?

    Franziska
  • 4. Re: Incorrect results returned by SQL Developer
    gary myers Explorer
    Currently Being Moderated
    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).
  • 5. Re: Incorrect results returned by SQL Developer
    608712 Newbie
    Currently Being Moderated
    Hi Franziska,

    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.

    Regards, Dieter
  • 6. Re: Incorrect results returned by SQL Developer
    589830 Newbie
    Currently Being Moderated
    Hi guys

    It was indeed the different NLS_DATE_FORMAT settings.

    Thank you so much for your help

    Franziska