Re: number vs. number(4)

In our last gripping episode hjp-usenet_at_SiKitu.wsr.ac.at (Peter J. Holzer) wrote:
> On 2001-01-24 01:38, Howard J. Rogers <howardjr_at_www.com> wrote:
> >Unqualified number fields are up to 38 digits in length (and hence
> >approximately 38 bytes variable),
>
> Are you sure? The output from dump looks like numbers are stored as
> variable length objects:
>
> SQL> desc foo
> Name Null? Type
> ----------------------------------------- -------- -----------
> I NUMBER(38)
>
> SQL> select i from foo;
>
> I
> ----------
> 1
> 123456789
>
> SQL> select dump(i) from foo;
>
> DUMP(I)
> ---------------------------------------------------------------------
> Typ=2 Len=2: 193,2
> Typ=2 Len=6: 197,2,24,46,68,90
>
> The number 1 seems to take 2 bytes, and the number 123456789 seems to
> take 6 bytes. Or am I misinterpreting the dump() function?
>
> hp
>
> --
> _ | Peter J. Holzer | All Linux applications run on Solaris,
> |_|_) | Sysadmin WSR | which is our implementation of Linux.
> | | | hjp_at_wsr.ac.at |
> __/ | http://www.hjp.at/ | -- Scott McNealy, Dec. 2000
>

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of the data argument passed to it. The following table lists the datatype codes and their meaning:

1 VARCHAR2(size)
 Variable-length character string having maximum length size bytes. Maximum size is 4000, and minimum is 1. You must specify size for VARCHAR2. 1 NVARCHAR2(size)
 Variable-length character string having maximum length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2. 2 NUMBER(p,s)
 Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

8 LONG
 Character data of variable length up to 2 gigabytes, or 231 -1 bytes.

12 DATE
 Valid date range from January 1, 4712 BC to December 31, 9999 AD.

23 RAW(size)
 Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.

24 LONG RAW
 Raw binary data of variable length up to 2 gigabytes.

69 ROWID
 Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

208 UROWID [(size)]
 Hexadecimal string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.

96 CHAR(size)
 Fixed-length character data of length size bytes. Maximum size is 2000 bytes. Default and minimum size is 1 byte.

96 NCHAR(size)
 Fixed-length character data of length size characters or bytes, depending on the choice of national character set. Maximum size is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum size is 1 character or 1 byte, depending on the character set.

112 CLOB
 A character large object containing single-byte characters. Both fixed- width and variable-width character sets are supported, both using the CHAR database character set. Maximum size is 4 gigabytes.

112 NCLOB
 A character large object containing multibyte characters. Both fixed- width and variable-width character sets are supported, both using the NCHAR database character set. Maximum size is 4 gigabytes. Stores national character set data.

113 BLOB
 A binary large object. Maximum size is 4 gigabytes.

114 BFILE
 Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

Your interpretation appears to be correct; the number 123456789 is stored as a 6-byte entity in the database.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/