#1093 DDL not generated OK for Oracle 11g

3.4.0
open
nobody
DDL (1)
6
2013-07-27
2013-07-26
Blaga Cosmin
No

DDL is not generated well for oracle table using oracle driver ojdbc14.jar.
I am using Squirrel SQL Client version 3.5.0. There is no group yet for it, so I posted the bug under 3.4.0 Milestone.
The DDL generates the create table with "decimal" type instead of "number", and it looses all precisions.
The bug is quite easy to reproduce:
create table scott.tmp_test(id number(20), float_number number(20,4));
[-img src=Create TMP_TEST.png alt=Create table script: missing =-]; [-img src=The created table.png alt=The created table: missing =-]
If you go to the [-img src=The created table_s DDL.png alt=DDL tab: missing =-] of the generated table it will show:
CREATE TABLE TMP_TEST
(
ID decimal(20),
FLOAT_NUMBER decimal(20)
);

3 Attachments

Related

Bugs: #1093

Discussion

  • Rob Manning
    Rob Manning
    2013-07-27

    The scale problem (numbers to the right of the decimal) is easy to fix. However, getting "NUMBER" to not be mapped to "DECIMAL" is a harder problem. The Oracle JDBC driver doesn't distinguish between the two when reporting metadata. For example, if you create both tables (one with NUMBER and one with DECIMAL), and look at the "columns" tab in SQuirreL, you should notice that the value of "DATA_TYPE" is 3, which in Java JDBC maps to java.sql.Types.DECIMAL. Java does support a "NUMERIC" type which has a "DATA_TYPE" value of 2 - but the Oracle driver doesn't report this. So that would be a bug report against the Oracle JDBC driver.

    Rob

     
  • Rob Manning
    Rob Manning
    2013-07-27

    I just checked in the fix for the scale problem. It will be in the SNAPSHOT channel soon. I'm not sure what the impact is of the other issue (DECIMAL instead of NUMBER). Is it the case that DECIMAL is a synonym for NUMBER ? I know that Oracle did that in the case of VARCHAR2 (where VARCHAR is a synonym). If so, then the impact would be aesthetic.

    Rob

     
  • Blaga Cosmin
    Blaga Cosmin
    2013-07-29

    I don't see any reference to the DECIMAL type here:
    http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209.
    Though if i run the create table sql with DECIMAL instead of NUMBER with
    Squirrel, the sql runs correctly.
    Though VARCHAR2 is a synonim to VARCHAR, there is a somewhat
    difference. VARCHAR2 stores exactly the length of the string, where
    VARCHAR stores exactly as much as it is declared. I.E. if you create a
    VARCHAR of 50 and a VARCHAR2 of 50, if you store a string of 10 in each,
    the VARCHAR will have a length of 50 , while the VARCHAR2 will have a
    length of 10. The length stored also depends on the semantics (char or
    byte), but that's another story.
    On 28/07/2013 1:56 AM, Rob Manning wrote:

    I just checked in the fix for the scale problem. It will be in the
    SNAPSHOT channel soon. I'm not sure what the impact is of the other
    issue (DECIMAL instead of NUMBER). Is it the case that DECIMAL is a
    synonym for NUMBER ? I know that Oracle did that in the case of
    VARCHAR2 (where VARCHAR is a synonym). If so, then the impact would be
    aesthetic.

    Rob


    [bugs:#1093] http://sourceforge.net/p/squirrel-sql/bugs/1093/ DDL
    not generated OK for Oracle 11g

    Status: open
    Labels: DDL
    Created: Fri Jul 26, 2013 07:40 AM UTC by Blaga Cosmin
    Last Updated: Sat Jul 27, 2013 12:11 PM UTC
    Owner: nobody

    DDL is not generated well for oracle table using oracle driver
    ojdbc14.jar.
    I am using Squirrel SQL Client version 3.5.0. There is no group yet
    for it, so I posted the bug under 3.4.0 Milestone.
    The DDL generates the create table with "decimal" type instead of
    "number", and it looses all precisions.
    The bug is quite easy to reproduce:
    create table scott.tmp_test(id number(20), float_number number(20,4));
    [-img src=Create TMP_TEST.png alt=Create table script: missing =-];
    [-img src=The created table.png alt=The created table: missing =-]
    If you go to the [-img src=The created table_s DDL.png alt=DDL tab:
    missing =-]
    of the generated table it will show:
    CREATE TABLE TMP_TEST
    (
    ID decimal(20),
    FLOAT_NUMBER decimal(20)
    );


    Sent from sourceforge.net because you indicated interest in
    https://sourceforge.net/p/squirrel-sql/bugs/1093/

    To unsubscribe from further messages, please visit
    https://sourceforge.net/auth/subscriptions/

     

    Related

    Bugs: #1093