This discussion is archived
3 Replies Latest reply: Jan 28, 2013 7:54 AM by ocp_2001 RSS

SQL query different result in Forms6i and Forms 10g

ocp_2001 Newbie
Currently Being Moderated
Can you think of any reason why the following statement would return different number from the same instance of a database.

The statement returns different number when run from Forms6i and Forms10g

Select to_char(sysdate,'D') from dual returns e.g. for Monday 2 in Forms6i and 1 in Forms10g

The statement runs in the database in a package executed from the form.

Any help would be welcome
  • 1. Re: SQL query different result in Forms6i and Forms 10g
    Paul M. Oracle ACE
    Currently Being Moderated
    Select to_char(sysdate,'D') from dual returns e.g. for Monday 2 in Forms6i and 1 in Forms10g
    I don't think the problem is the Forms version. See this example :
    C:\>set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
    
    C:\>sqlplus test/test
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 25 17:16:49 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    SQL> Select to_char(sysdate,'D') from dual;
    
    T
    -
    6
    
    SQL> exit
    Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    C:\>set NLS_LANG=ITALIAN_ITALY.WE8MSWIN1252
    
    C:\>sqlplus test/test
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Ven Gen 25 17:17:31 2013
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connesso a:
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
    
    SQL> Select to_char(sysdate,'D') from dual;
    
    T
    -
    5
    
    SQL>
  • 2. Re: SQL query different result in Forms6i and Forms 10g
    Michael Ferrante (Oracle) Guru Moderator
    Currently Being Moderated
    Well to start with, Forms 6 and 10 do not use the same db client version. So differences in behavior are certainly possible. That said, I believe the NLS settings are what will cause such a difference.

    Take a look at this:

    Day of week (1-7) and NLS settings
  • 3. Re: SQL query different result in Forms6i and Forms 10g
    ocp_2001 Newbie
    Currently Being Moderated
    Thanks for your replies.

    As you suggested, the problem was related to NLS_LANG settings.

    In our set-up in form6i NLS_LANG settings were being obtained from the registry of the local machine running the forms but in forms 10g the setting is being obtained from the database.

    I have employed a work around to use 'DY' instead of 'D' format to determine the day of the week which meets my requirement.

    Thanks

    Edited by: ocp_2001 on Jan 28, 2013 3:53 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points