Built-in Datatypes

Oracle's built-in datatypes include character, numeric, datetime, large object, ROWID, and long/raw. For the most part, you will use character, numeric, and datetime datatypes in your applications. If you have applications with high-resolution photos or video that you want to store in the database, you will most likely use large object (LOB) datatypes, either the built-in CLOB (character large object) or BLOB (binary large object) datatypes or Oracle-supplied datatypes such as ORDVideo, ORDAudio, and ORDImage.

You will rarely use ROWID datatypes in your applications; they are primarily used internally in indexes and for very specialized applications. Similarly, you will most likely not use long, raw, or long/raw datatypes, as they are included in Oracle Database XE for backward-compatibility with applications written in previous versions of Oracle.

Character Datatypes

You store alphanumeric data in character datatypes, using either the database character set or the Unicode character set. You create your database with a specified character set. For example, in the United States you may use the US7ASCII character set to support the English language and any other language that uses a subset of the English language. To expand your multilingual support in the database, you can create your database using the WE8ISO8859P1 character set, which supports English and other European languages such as German, French, and Spanish. With the support for Unicode in PHP version 5.x, you can specify AL16UTF16 to support character sets from all known written languages in your Web applications.

If you use multilingual character sets, you must be aware of the storage consequences for character data; depending on the character being stored in the column, Oracle may require anywhere from 1 to 4 bytes to store a given character. Therefore, when you define the length of your character strings, you must be cognizant of whether you are using byte semantics or character semantics. Byte semantics assumes a single-byte character set, where one character always requires one byte of storage. On the other hand, character semantics permits you to define the lengths of your character strings as the number of characters, regardless of how many bytes each character requires. Byte semantics is the default unless you override it using the initialization parameter NLS_LENGTH_SEMANTICS.

■Note Even if you do not define a character set at database creation that is sufficient to support your multilingual needs, you can use the explicit Unicode datatypes: NCHAR, NVARCHAR, and NCLOB.

Table 30-1 lists the character datatypes and their qualifiers.

Table 30-1. Character Datatypes

Character Datatype Name

Description

CHAR(size [BYTE|CHAR])

Fixed width character string that always stores size bytes when you specify BYTE, or characters if you specify CHAR, right-padded with spaces. Default size specification is BYTE unless you specify CHAR.

NCHAR(size)

Same as CHAR but uses Unicode; size is always in characters.

VARCHAR2(size [BYTE|CHAR])

Variable width character string; stores only the number of bytes or characters assigned up to size bytes or characters. Default size specification is BYTE unless you specify CHAR. The datatype VARCHAR is equivalent to VARCHAR2, but Oracle reserves the right to change the definition of VARCHAR in future releases.

NVARCHAR2(size [BYTE|CHAR])

Same as VARCHAR2, but uses Unicode; size is always in characters.

LONG

A deprecated character datatype included for backward-compatibility; Oracle may remove this datatype in future releases. Stores variable-length alphanumeric character strings up to 2GB in size. The CLOB datatype has all of the features of LONG and none of the restrictions (such as only one LONG column per table); therefore, Oracle strongly recommends using the CLOB datatype (discussed later in this chapter in the section "Large Object Datatypes").

There are many situations where you should use NVARCHAR2 to support your application—for example, if you want to create your table once for all of your worldwide branch offices. In this example, you create a table to contain a list of customers whose name and country code are stored in the database character set. The address, city, and postal code can be any address in the world, and as a result you need to ensure that it is stored using the Unicode character set:

create table all (cust_id cust_name country_code address_line address_line address_line city region postal_code );

We will talk more about creating tables later in this chapter in the section "Creating and Maintaining Tables."

cust number, varchar2(100), char(3),

1 nvarchar2(75),

2 nvarchar2(75),

3 nvarchar2(75), nvarchar2(100), nvarchar2(100), nvarchar2(25)

Numeric Datatypes

Numeric datatypes store positive and negative fixed-point and floating-point numbers, as well as a floating point representation for infinity and Not A Number (only for values imported from Oracle version 5). For all numeric datatypes, the maximum precision is 38 digits. The three numeric datatypes are NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. Table 30-2 lists the numeric datatypes and their qualifiers.

Table 30-2. Numeric Datatypes

Numeric Datatype Name Description

NUMBER[(precision[,sco!e])] Stores zero, positive, and negative numbers with a default precision of 38. The scale is the number of digits to the right of the decimal point and defaults to 0. The range for scale is -84 to 127.

BINARY_FLOAT Stores single-precision floating-point numbers with 32 bits of precision.

BINARY_DOUBLE Stores double-precision floating-point numbers with 64 bits of precision; otherwise same as BINARY_FLOAT.

NUMBER Datatype

Numeric values stored in a NUMBER column are stored in scientific notation in variable-length format with up to 38 digits of precision. One byte of the internal representation is for the exponent; the mantissa uses up to 20 bytes with two digits stored in each byte. Therefore, the number 24632 uses more storage space than the number 120000000000.

■Note You can calculate the column size, in bytes, to store a numeric value in a column defined as NUMBER(p), where p is the precision of a given numeric value, by using the formula ROUND((length(p)+s)/2))+1, where s is 0 if the number is positive, and 1 if the number is negative.

If you know the values you expect to store in a NUMBER column, it's a good idea to specify the precision and scale to enforce domain definitions and data integrity. For example, defining a column to store gender as NUMBER(1) with a CHECK constraint (we explain CHECK constraints later in the section "Using Constraints") prevents a programmer or data entry clerk from using this column to store a three-digit value for a country code along with the gender, for example.

Table 30-3 shows how the number 1,234,567.89 is stored internally for different NUMBER datatype specifications.

Table 30-3. Numeric Datatype Rounding

Numeric Input Value

NUMBER Definition

Stored As

1,234,567.89

NUMBER

1234567.89

1,234,567.89

NUMBER(9)

1234567

1,234,567.89

NUMBER(6)

Error condition, precision too big

1,234,567.89

NUMBER(9,1)

1234567.9

1,234,567.89

NUMBER(9,2)

1234567.89

1,234,567.89

NUMBER(7,-2)

1234500

Notice that no error occurs when Oracle rounds a number because of the scale, but Oracle will generate an error if you exceed the precision of the NUMBER definition.

BINARY_FLOAT and BINARY_DOUBLE

The BINARY_FLOAT and BINARY_DOUBLE datatypes support all functionality provided by NUMBER but use binary precision. This has both advantages and disadvantages; arithmetic calculations are typically faster and take less storage than NUMBER datatypes but cannot represent numbers such as 0.1 exactly. In practice, however, you will rarely encounter problems with rounding errors, such as obtaining a result of 0.09999999999 when you are expecting 0.1. Oracle's floating-point numeric representation conforms to most of the IEEE Standard for Binary Floating-Point Arithmetic, IEEE 754. One typical use for BINARY_FLOAT and BINARY_DOUBLE is for statistical analysis where you will most likely need efficient handling of calculation-intense queries. In addition, you can save space in your tables if you have high-precision values. A BINARY_DOUBLE value will always take up 8 bytes of storage, whereas a NUMBER may use up to 21 bytes at the maximum precision.

Was this article helpful?

0 0

Post a comment