This discussion is archived
13 Replies Latest reply: Oct 9, 2012 11:38 AM by Gary Graham RSS

Bug with latest SQLDeveloper with numbers beyond Java's MAXINT

jmsjr Newbie
Currently Being Moderated
General Info:

SQLDeveloper Version 3.2.10.09 ( 64-bit Windows )
Build MAIN-09.57

Connected to an Oracle11g database


This is quite straightforward. Try the following query:

select 2147483648 as col1, 2147483647 as col2 from dual;

Execute it by highlighting the query and hitting F5 to make sure the output goes to the "Script Output" tab. You get the following output ( Notice that COL1 has no output ).

COL1 COL2
---- ----
2147483647

If you execute the query by higlighting the query and hitting F9, so that the output goes to the "Query Result" tab, the correct results are shown.


The output is self explanatory. The number 2147483648 is 1 number beyond Java's MAXINT value.

Edited by: user10739357 on 04-Oct-2012 02:13
  • 1. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!
    >
    Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    >
    Might indeed be a bug. This produces the correct results using version 3.0.02 on XP connecting to 11.2.

    Can you provide more information about your environment: OS and version?
  • 2. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    FlyingGuy Explorer
    Currently Being Moderated
    Hey there,

    I ran
    select 2147483648 as col1, 2147483647 as col2 from dual;
    the result was:
    2147483648   / 2147483647 respectively 
    Java(TM) Platform     1.6.0_32
    Oracle IDE     3.2.10.09.57
    Versioning Support     3.2.10.09.57
    Open-Suse uname -a = 3.1.10-1.16-desktop #1 SMP PREEMPT Wed Jun 27 05:21:40 UTC 2012 (d016078) x86_64 x86_64 x86_64 GNU/Linux

    Edited by: FlyingGuy on Oct 4, 2012 11:21 AM
  • 3. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    So it looks like none of us can reproduce that behavior, even though you provided an excellent test case and the basic environment info. Perhaps the issue is related to your OS or the type of connection you use? Is it basic, TNS, etc? Does your Database | Advanced preference specify using the OCI/Thick driver?

    Did you issue any
    COL col1 FORMAT ...
    command prior to running the select?

    If the smaller number is used in both columns, do you get the expected result?

    Internally SQL Developer seems to have SET NUMWIDTH 10 hardwired, but allows SET NUMFORMAT as in SQL*Plus. There was a bug earlier this year related to numeric overflow in bind variables that has been fixed for the next release, but that does not seem to be your issue:
    NUMBER - numeric overflow

    Regards,
    Gary
    SQL Developer Team
  • 4. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    jmsjr Newbie
    Currently Being Moderated
    Hi,

    Thanks guys .. it may be related to the JVM / JDK that I am using, as I am using JDK 1.7.0_06 ... More details below.
    Gary Graham wrote:
    Hi,

    So it looks like none of us can reproduce that behavior, even though you provided an excellent test case and the basic environment info. Perhaps the issue is related to your OS or the type of connection you use? Is it basic, TNS, etc? Does your Database | Advanced preference specify using the OCI/Thick driver?
    OS: Windows 7 64-bit

    Contents of sqldeveloper.conf:

    IncludeConfFile ../../ide/bin/ide.conf

    SetJavaHome C:\java\jdk1.7.0_06\x64

    AddVMOption -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true

    AddVMOption -Dsun.java2d.ddoffscreen=false

    AddVMOption -Dwindows.shell.font.languages=

    AddVMOption -XX:MaxPermSize=256M

    AddVMOption -Doracle.jdbc.mapDateToTimestamp=false

    IncludeConfFile sqldeveloper-nondebug.conf

    AddVMOption -XX:+UnlockExperimentalVMOptions
    AddVMOption -XX:+UseG1GC
    AddVMOption -XX:+UseFastAccessorMethods
    AddVMOption -XX:+AggressiveOpts
    AddVMOption -XX:+TieredCompilation


    Java:
    c:\java\jdk1.7.0_06\x64\bin\java -version
    java version "1.7.0_06"
    Java(TM) SE Runtime Environment (build 1.7.0_06-b24)
    Java HotSpot(TM) 64-Bit Server VM (build 23.2-b09, mixed mode)


    >
    Did you issue any
    COL col1 FORMAT ...
    No.
    command prior to running the select?

    If the smaller number is used in both columns, do you get the expected result?
    Yes

    >
    Internally SQL Developer seems to have SET NUMWIDTH 10 hardwired, but allows SET NUMFORMAT as in SQL*Plus. There was a bug earlier this year related to numeric overflow in bind variables that has been fixed for the next release, but that does not seem to be your issue:
    NUMBER - numeric overflow

    Regards,
    Gary
    SQL Developer Team
  • 5. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    jmsjr Newbie
    Currently Being Moderated
    jmsjr wrote:
    Contents of sqldeveloper.conf:

    IncludeConfFile ../../ide/bin/ide.conf

    SetJavaHome C:\java\jdk1.7.0_06\x64
    I've just tried using 64-bit JDK 1.6.0_34 by modifying the above to:

    SetJavaHome C:\java\jdk1.6.0_34\x64


    .. restarted SQLDeveloper, with still the same issue.
    Went to "About Oracle SQL Developer" and the "Version" tab just to make sure it is using JDK 1.6:


    About
    -----

    Oracle SQL Developer 3.2.10.09
    Version 3.2.10.09
    Build MAIN-09.57
    Copyright © 2005, 2012 Oracle. All Rights Reserved.
    IDE Version: 11.1.1.4.37.59.48
    Product ID: oracle.sqldeveloper
    Product Version: 11.2.0.09.57

    Version
    -------

    Component     Version
    =========     =======
    Java(TM) Platform     1.6.0_34
    Oracle IDE     3.2.10.09.57
    Versioning Support     3.2.10.09.57
  • 6. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    jmsjr Newbie
    Currently Being Moderated
    Gary Graham wrote:
    Hi,

    So it looks like none of us can reproduce that behavior, even though you provided an excellent test case and the basic environment info. Perhaps the issue is related to your OS or the type of connection you use? Is it basic, TNS, etc? Does your Database | Advanced preference specify using the OCI/Thick driver?
    * Both TNS and Basic connection types show the same issue.
    * Database -> Advanced preference shows that "Use OCI/Thick driver" is NOT ticked.

    Thanks.
  • 7. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    jmsjr Newbie
    Currently Being Moderated
    So it looks like none of us can reproduce that behavior, even though you provided an excellent test case and the basic environment info. Perhaps the issue is related to your OS or the type of connection you use? Is it basic, TNS, etc? Does your Database | Advanced preference specify using the OCI/Thick driver?
    * Both Basic and TNS connections show the same issue
    * Database -> Advanced shows that "Use OCI/Thick driver" is NOT ticked.

    Thanks.
  • 8. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    jmsjr Newbie
    Currently Being Moderated
    rp0428 wrote:
    Welcome to the forum!
    >
    Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    >
    Might indeed be a bug. This produces the correct results using version 3.0.02 on XP connecting to 11.2.
    I did not have that issue with version 3.0.02 nor with 64-bit 3.1.07 on 64-bit Windows7 or any prior versions.
    It only started, or at least I only noticed it, with the latest version ( 64-bit 3.2.10 ), which I downloaded and installed yesterday.

    Having said that, I have been querying tables in SQLDeveloper ( with previous versions ) where the PK is a number that have values that go beyond Java's MAXINT in one of our databases for .. maybe the past 4 years.
  • 9. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    Kofi Journeyer
    Currently Being Moderated
    Hmm,I can reproduce this issue.
    In script output it looks something like
    COL1 COL2
    ----- ----
    2147483647

    whilst from the query output is correct (2147483648/2147483647).

    OS: Windows 7 64 bit
    SQL Developer : 3.2.10.09 (64 bit)
    Java: jdk1.6.0_30 (64 bit)

    Sqlldeveloper Conf
    IncludeConfFile ../../ide/bin/ide.conf
    
    SetJavaHome C:\Program Files\Java\jdk1.6.0_30
    
    AddVMOption  -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true
    
    AddVMOption -Dsun.java2d.ddoffscreen=false
    
    AddVMOption -Dwindows.shell.font.languages=
    
    AddVMOption  -XX:MaxPermSize=512M
    AddVMOption -Xmx1024M
    AddVMOption -XX:+UnlockExperimentalVMOptions
    AddVMOption -XX:+UseG1GC
    AddVMOption -XX:+G1YoungGenSize=25m
    AddVMOption -XX:+G1ParallelRSetUpdatingEnabled
    AddVMOption -XX:+G1ParallelRSetScanningEnabled 
    
    AddVMOption -Doracle.jdbc.mapDateToTimestamp=false  
    
    IncludeConfFile  sqldeveloper-nondebug.conf
    Kofi
  • 10. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    I must have way too many SQL Developer releases installed. In fact, the problem reproduces in 3.2.10.09.57 but not 3.2.09.30. I'll track down any worksheet formatting bug fixes between the two and log a bug for the regression if QA/Dev has not already caught it.

    Thanks for reporting,
    Gary
  • 11. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    Gary Graham Expert
    Currently Being Moderated
    Here is a workaround for 3.2.10.09.57. If the worksheet contains this...
    show numformat
    select 2147483648 as col1, 2147483647 as col2 from dual;
    set numformat 9999999999
    show numformat
    select 2147483648 as col1, 2147483647 as col2 from dual;
    the scripted output yields this...
    numformat ""
    COL1 COL2
    ---- ----
         2147483647 
    
    numformat "9999999999"
    COL1 COL2
    ---- ----
     2147483648  2147483647 
    If numformat defaults to a zero-length string, the number is treated as a signed 32-bit integer. Otherwise the numformat should apply. I will ping the developer.

    -Gary
  • 12. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    jmsjr Newbie
    Currently Being Moderated
    Gary Graham wrote:
    Here is a workaround for 3.2.10.09.57. If the worksheet contains this...
    show numformat
    select 2147483648 as col1, 2147483647 as col2 from dual;
    set numformat 9999999999
    show numformat
    select 2147483648 as col1, 2147483647 as col2 from dual;
    the scripted output yields this...
    numformat ""
    COL1 COL2
    ---- ----
    2147483647 
    
    numformat "9999999999"
    COL1 COL2
    ---- ----
    2147483648  2147483647 
    If numformat defaults to a zero-length string, the number is treated as a signed 32-bit integer. Otherwise the numformat should apply. I will ping the developer.

    -Gary
    Confirmed that this workaround does work.

    Is there anyway of saving / setting the numformat ... so that it becomes the default numformat whenever you start SQLDeveloper ?
  • 13. Re: Bug with latest SQLDeveloper with numbers beyond Java's MAXINT
    Gary Graham Expert
    Currently Being Moderated
    Is there anyway of saving / setting the numformat
    Use the Filename for connection startup script setting in Tools | Preferences | Database. Just add that setting to your script.

    As for the bug itself (Bug 14733141), the developer has fixed it for the next release.

    -Gary