This discussion is archived
3 Replies Latest reply: Mar 28, 2012 1:44 PM by Frank Kulash RSS

Struggling with NUMBER(20) Field

926991 Newbie
Currently Being Moderated
Hello Everyone,

I am trying to create a field to hold data for a set of Card Numbers. This is what I have entered in my syntax for creating the table:

Card_Number          NUMBER(20)     NOT NULL,


Now When I insert my data into ISQL PLus via the Insert Statement :

INSERT INTO PaymentVALUES ('1245784524542135'); AND I SELECT * FROM Payment I Get the following:

CARD_NUMBER
*1.2458E+15*


Why do I get a complete wrong number..

All help is well appreciated!!

THANK YOU!!

Edited by: 923988 on Mar 28, 2012 6:19 AM
  • 1. Re: Struggling with NUMBER(20) Field
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    923988 wrote:
    Hello Everyone,

    I am trying to create a field to hold data for a set of Card Numbers. This is what I have entered in my syntax for creating the table:

    Card_Number          NUMBER(20)     NOT NULL,


    Now When I insert my data into ISQL PLus via the Insert Statement :

    INSERT INTO PaymentVALUES ('1245784524542135'); AND I SELECT * FROM Payment I Get the following:

    CARD_NUMBER
    *1.2458E+15*


    Why do I get a complete wrong number..
    Actually, you get an <b>in</b>complete right number.
    By default, SQL*Plus uses 10 characters to display a number. So the number 1/7 would be displayed as
    .142857143
    even though the actual value is .142857142857142857... SQL*Plus doesn't have room to display the entire number, so it displays a rounded approximation of the number, showing the most significant part. This does not change how the number is stored; only how it is displayed.
    The same kind of thing happens when it needs to display an integer that has more than 10 digits. Since SQL*Plus only has 10 spaces to use, it can't show the exact number that's stored, so it shows the most significant part, in scientific notattion, in this case 1.2458 times 10 to the 15th power.

    To show more of the number, you can use the SQL*Plus COLUMN command to specify a particular format for that column. For example
    COLUMN  card_number   FORMAT  99999999999999999999
    After giving that command, SQL*Plus will use 20 spaces (since there are 20 9's in the command) to show the column called card_number, and continue doing so until you end the SQL*Plus session (or override it with another COLUMN command).

    To change the default width for all NUMBER columns at once, you can use the SQL*Plus command
    SET  NUMWIDTH  20
    For more, look up the SET command in the SQL*Plus manual:
    http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm#BACGAJIC

    You can also use the TO_CHAR function to convert the number to a string, in a given format.

    Edited by: Frank Kulash on Mar 28, 2012 11:08 AM
  • 2. Re: Struggling with NUMBER(20) Field
    926991 Newbie
    Currently Being Moderated
    Hello,

    Thanks for your reply.

    Where do I actually insert what you said?

    COLUMN card_number FORMAT 99999999999999999999

    Thanks
  • 3. Re: Struggling with NUMBER(20) Field
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    923988 wrote:
    Hello,

    Thanks for your reply.

    Where do I actually insert what you said?

    COLUMN card_number FORMAT 99999999999999999999
    Issue that command before the first query where you want it to take effect.
    For example, if I start a new SQL*Plus session and run this script:
    -- This query is exactly the same as the one below
    SELECT       dname
    ,       deptno
    FROM       scott.dept
    ORDER BY  deptno
    ;
    
    -- Using COLUMN command to make deptno longer
    COLUMN  deptno     FORMAT  99999999999999999999
    
    
    -- This query is exactly the same as the one above
    SELECT       dname
    ,       deptno
    FROM       scott.dept
    ORDER BY  deptno
    ;
    Then I get this output:
    DNAME              DEPTNO
    -------------- ----------
    ACCOUNTING             10
    RESEARCH               20
    SALES                  30
    OPERATIONS             40
    
    
    DNAME                         DEPTNO
    -------------- ---------------------
    ACCOUNTING                        10
    RESEARCH                          20
    SALES                             30
    OPERATIONS                        40
    You might want to put the COLUMN command in your LOGIN.SQL file, so that the card_number column will automatically be formatted whenever you go into SQL*Plus.

Legend

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