Home » SQL & PL/SQL » SQL & PL/SQL » Number Datatype Format
Number Datatype Format [message #9324] Tue, 04 November 2003 10:24 Go to next message
Muhammad Waqas Shabir
Messages: 26
Registered: April 2003
Junior Member
I am storing e.g 0.56 in a number data type column but when i retrive that value. It shows 1.

To my understanding oracle is rounding my numbers. I dont want this to happen as this figure is of currency e.g i want to store o.234 million. how would i be able to do it.

How will i be performing calculations precisely?

Thanking u in anticipation.

Waqas
Re: Number Datatype Format [message #9325 is a reply to message #9324] Tue, 04 November 2003 10:43 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Is the number defined on your table as something like NUMBER(10)? What are the results of DESCRIBE my_table?

What do you get when you issue SELECT * FROM table WHERE my_column = 0.56?

What are the results when you issue SHOW NUMFORMAT and SHOW NUMWIDTH in SQL*Plus?

Do you have any formatting on the column?
SQL> CREATE TABLE t (n NUMBER);
  
Table created.
  
SQL> INSERT INTO t VALUES (0.56);
  
1 row created.
  
SQL> COMMIT;
  
Commit complete.
  
SQL> COLUMN n FORMAT 9999
SQL> SELECT n
  2  FROM   t
  3  /
  
    N
-----
    1
  
SQL> CLEAR COLUMN
columns cleared
SQL> SELECT n
  2  FROM   t
  3  /
  
         N
----------
       .56
  
SQL> 
HTH,

A.
Re: Number Datatype Format [message #9345 is a reply to message #9325] Wed, 05 November 2003 07:58 Go to previous messageGo to next message
Muhammad Waqas Shabir
Messages: 26
Registered: April 2003
Junior Member
I have the created a column like this create table ABC( P_DisbN25 NUMBER(25));

In above statement, i have tried to store a 25 digits number in my column "P_DisbN25". Am i doing the right thing for storing currency values to it?

I am not applying any format neither at insertion nor at retrieval.

So, R u getting my point now?

Do explain/recommend any database design changes or query format changes with examples.

Thanks
Waqas
Re: Number Datatype Format [message #9346 is a reply to message #9345] Wed, 05 November 2003 08:28 Go to previous message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
You should read the documentation on the NUMBER datatype:
----------------------------------------------------------------------
[size=3][font=Arial, Helvetica, sans-serif]NUMBER Datatype[/font][/size]

The [i]NUMBER[/i] datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10[sup]-130[/sup] and 9.9...9 x 10[sup]125[/sup] (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10[sup]126[/sup], then Oracle returns an error.

Specify a fixed-point number using the following form:
[code]NUMBER(p,s) [/code]

where:
[list][*][i][i]p[/i][/i] is the [b]precision[/b], or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.[*][i][i]s[/i][/i] is the [b]scale[/b], or the number of digits to the right of the decimal point. The scale can range from -84 to 127.[/list]

Specify an integer using the following form:
[code]NUMBER(p)[/code]

This represents a fixed-point number with precision p and scale 0 and is equivalent to [i]NUMBER(p,0)[/i].

Specify a floating-point number using the following form:
[code]NUMBER[/code]

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

----------------------------------------------------------------------

By setting up your column as a NUMBER(25), you are saying that you wish to store only integer values in this column.

Why not just define your column as a plain old NUMBER and be done with it? (I.e., CREATE TABLE abc (p_disbn25 NUMBER);).)

A.
Previous Topic: select all columns except one from a table
Next Topic: PL/SQL ....ok this is driving me nuts
Goto Forum:
  


Current Time: Thu Mar 28 23:58:25 CDT 2024