Re: NUMBER data type question

In article <8agk0g$8hc$1_at_nnrp1.deja.com>,   david_petit_at_yahoo.com wrote:
> Hello all,
>
> When I create table with column a with numeric data type as
 follows:
>
> SQL> create table testing (a number(15,2));
>
> Table created.
>
> SQL> desc testing;
> Name Null? Type
> ------------------------------- -------- ----
> testing NUMBER(15,2)
>
> When I query the all_tab_columns, the definition of a in testing
 is:
>
> DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE N COLUMN_ID
> DEFAULT_LENGTH
> --------- ----------- -------------- ---------- - ---------
> --------------
> NUMBER 22 15 2 Y 1
>
> Now, I drop the table and create again:
>
> SQL> create table testing (a number);
>
> Table created.
>
> SQL> desc testing;
> Name Null? Type
> ------------------------------- -------- ----
> testing NUMBER
>
> When I query the all_tab_columns, the definition of a in testing
 is:
>
> DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE N COLUMN_ID
> DEFAULT_LENGTH
> --------- ----------- -------------- ---------- - ---------
> --------------
> NUMBER 22 Y 1
>
> My question is, why the data length of both table definitions are
 22?
> Should I ignore the value of DATA_LENGTH column when the data type is
> NUMBER? If yes, how can I know the exact value of DATA_LENGTH if the
> data type is NUMBER and DATA_PRECISION is NULL value?
>
> Thanks,
> David
>

Numbers are stored as variable length scientific notation in an internal format with a default of 38 digits of precision. The length depends on the value. The 22 is a maximum and not the actual space used. For a number declared as (15,2) you saw the precision and scale in all_tab_columns because the scale is fixed and so is the maximum precision, 15, but when you use number with no precision or scale the actual precision and scale will depend on the data value stored, 999.123 vs 999.12345.

The storage length formula is in the Concepts manual. You might find it interesting to select a number column with a wide range of stored values using the length and dump functions.  --
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Mar 12 2000 - 00:00:00 CST