number replaced by char

[Praveen Kumar Singh]

HI,

I have to store a account no which is 12(112345678900) digit in length. so in create statement can i use CHAR instead or NUMBER.

PKS

[Martin Vashko]

You don't need a CHAR for that. Oracle's NUMBER can store up to 38 significant digits:SQL> create table test(n number); Table created. SQL> insert into test(n) values(0000000000100000000020000000003000000004000000000); 1 row created. SQL> insert into test(n) values(1234567890123456789012345678901234567891234567890); 1 row created. SQL> -- Make SqlPlus display all digits SQL> set numwidth 50 SQL> select n from test; N -------------------------------------------------- 100000000020000000003000000004000000000 1234567890123456789012345678901234567890000000000 SQL>

[Praveen Kumar Singh]

Thanks but when i am writing a select statement the Zero(0). when i inserted i pass the value with Zero.

SQL> SELECT account_no, total_salary from salary;

     ACCOUNT_NO    TOTAL_SALARY
--------------- ---------------
    29701522043            9500
    29701522054           12000
    29701522062           12800
    29701522056            9400
    29701522078            7500
    29701522099           17100
    29701522012            1000

7 rows selected.

[Martin Vashko]

So the problem is that you inserted 012345678912, but select gives you 12345678912?

When you store a number, the exact formatting (leading zeroes, trailing zeroes, superfluous plus sign and so on) are not stored. You'll have to format the number (either in the database using the TO_CHAR function, or in the client application) to convert it to the format you specify. The format can be constructed so that it provides the leading zeroes, eg.:select to_char(123, 'FM000000000000') fmt from dual;(The FM modifier in Oracle format ensures there won't be a leading space).

There might still be a valid requirement to store the account number as a text, and not as a number (for example, if you envision different account number formats that would include non-numerical characters, such as dashes or slashes). Ultimately, the decision is on you, because no one else can know your exact situation. But even if you decide to store the account number as text, use a VARCHAR2 column, not CHAR. VARCHAR2 is much easier to expand if, all of sudden, you find that you need to store account numbers longer than 12 digits/characters. And if I was storing account number as text, I'd put constraints in place that would prevent invalid account numbers from ever been stored in the database.