3 Replies Latest reply: Jan 28, 2013 9:54 AM by ocp_2001 RSS

    SQL query different result in Forms6i and Forms 10g

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