From: nolan@helios.unl.edu (Michael Nolan) Newsgroups: comp.databases.oracle Subject: Re: Oracle Datatypes Question Date: 3 Mar 1995 19:44:20 GMT Organization: University of Nebraska--Lincoln Lines: 41 Message-ID: <3j7rik$8m9@crcnis3.unl.edu> References: <3iv8ri$69q@stc06.CTD.ORNL.GOV> Reply-To: nolan@helios.unl.edu NNTP-Posting-Host: helios.unl.edu dcrowson@amoco.com (Dave Crowson) writes: >In article <3iv8ri$69q@stc06.CTD.ORNL.GOV> fhb@ornl.gov (Frances Butler) writes: >>I'm a newbie to Oracle, and have just begun to do some reading on >>this subject. I have consulted Oracle documentation, but I'm >>still confused. Here's the question: can you provide a brief >>novice-type explanation of when one would use an integer datatype >>versus a number datatype? >Use INTEGER for storing whole numbers , like, err, integers >and use NUMBER for storing non-integer values, like 4,567 or 123.6789 ^^^^^ ^^^^^^^^ This attempt at satire seems to fall a bit short. To provide a more useful explanation, all numeric data in Oracle is stored in the same format, regardless of how it is specified. The format is well-documented in the Oracle Concepts manual and guarantees 38 digits of precision in up to 22 bytes of storage. It would be possible from a design point of view to declare all numeric data as NUMBER. However, there are advantages to being more specific in the definition. It permits the database to enforce the desired data range and decimal precision. For example, specifying a field as NUMBER(4) will ensure that it contains an integer between -9999 and 9999 inclusive. Numbers outside that range will be rejected, and non-integers will be rounded to integer values. Although I don't think Oracle takes much advantage of it, it would also be possible to utilize the precision information in allocating data space. (A NUMBER field might conceivably require the full 22 bytes of the internal numeric format to hold a 38 digit number, a NUMBER(4) field would never require the full 22 bytes, so why allow for it in determining freespace within a block?) The INTEGER datatype is an ANSI concept, not an Oracle one. It is converted to NUMBER(38) by Oracle. --- Michael Nolan, Sysop for the DBMS RoundTable on GEnie nolan@notes.tssi.com, dbms@genie.geis.com (posted from nolan@helios.unl.edu)