Oracle Datatypes

Each variable and value in Oracle has a datatype. Therefore, to successfully insert, manipulate, and update data in an Oracle database, you should be familiar with the datatypes. Don't worry, we won't cover every detail of Oracle datatypes; that would take the entire book. But we will introduce you to the datatypes you're most likely to use in your web development efforts. Before we dive in, it's helpful to know that Oracle stores data in one of three basic datatype families—character, numeric, and date.

Character Datatypes

Character data can be any string of data that will not be the object of an arithmetic operation. Basic character datatypes are CHAR, VARCHAR, NCHAR, NVARCHAR, LONG, and LONG RAW.

Note 

Foreign languages often require special characters and character sets. NCHAR, NVARCHAR, and NCLOB can store national character set data, making these datatypes ideal for multilanguage applications. National character sets are lists of characters and character codes specific to a country or territory.

CHAR

CHAR is a fixed-length character string. The minimum size is 1 byte, and the maximum size is 2000 bytes. You are not required to specify a size with this character data. The default size is 1 byte.

VARCHAR

VARCHAR is a variable-length character string. The minimum size is 1 byte, and the maximum size is 4000 bytes. You must specify size when specifying a VARCHAR datatype.

NCHAR

NCHAR is a fixed-length character string. NCHAR can store national character set data.

NVARCHAR

NVARCHAR is a variable-length character string. The maximum size can be specified in characters or bytes. The number of bytes required to store each character determines the maximum size, with an upper limit of 4000 bytes. You must specify size when using an NVARCHAR datatype. NVARCHAR can store national character set data.

LONGC

haracter data of variable length with a maximum of 2 gigabytes, or 2³¹ - 1 bytes.

LONG RAW

Raw binary data of variable length with a maximum of 2 gigabytes.

Large Datatypes

In addition to character data, Oracle provides several datatypes that support enormous amounts of information. These datatypes are typically used to store an unknown size or mixture of text, images, sound, video, and so on. Large datatypes are BLOB, CLOB, NCLOB, and BFILE.

BLOB

BLOB can hold a binary object with a maximum size of 4 gigabytes. Use this datatype to store images, sound, video, and so on.

CLOB

CLOB is a character object that can hold a maximum size of 4 gigabytes. Use CLOB to store large amounts of text.

NCLOB

NCLOB is a character object that can store a maximum size of 4 gigabytes. NCLOB can store national character set data. Use NLOB to store large amounts of text that can be encoded with different language character sets.

BFILE

BFILE contains a pointer to a large binary file located outside the database. BFILE's maximum size is 4 gigabytes.

Numeric Datatypes

Although it has half a dozen character datatypes, Oracle stores all numeric values under the NUMBER datatype. Numeric data can be any data that can take part in an arithmetic operation. Because all numeric data is grouped into one datatype and because numeric data can have a fractional component, it is highly suggested to set the precision and the scale.

Precision refers to the maximum number of digits in the value. Scale defines the number of digits pointing to the fractional component of the number, or the number of digits to the right of the decimal point. If you omit scale from the datatype, values are treated as integer numbers, and no decimal portion is stored. If you omit both scale and precision, the value is treated as a floating-point number. To demonstrate, you can use the following SQL statement to create a NUMERIC datatype with a precision of 12 and a scale of 6.

CREATE TABLE EXAMPLE (  NUMBER_U NUMERIC(12,6) )

The column this SQL statement creates can hold a maximum of 999,999.999999. Likewise, if you change the precision (the first number) of the numeric datatype to 7, the column can hold 9.999999. If you would like to set the datatype to maximum precision, use an asterisk as shown in the following SQL statement.

CREATE TABLE EXAMPLE (  NUMBER_U NUMERIC(*,6) )

Date Datatypes

The DATE datatype stores date and time information. It's important to note that Oracle stores date data in a proprietary format using the following information:

• Century

• Year

• Month

• Day

• Hour

• Minute

• Second

This format is slightly different from most other date formats, such as the ODBC (Open Database Connectivity) data format. Unfortunately, passing a date variable with a slight variant to Oracle is problematic. To store a value in a DATE datatype, the date value must be converted into the Oracle date format, using the Oracle To_Date function.

In most cases, Oracle can perform this conversion automatically. By default, Oracle converts a date in the DD-MON-YY format to its internal date format. Be forewarned, this default date format is specified in Oracle setup files and can be changed, so be sure to check with your Oracle DBA for the appropriate date format.

Note 

The DD-MON-YY is a format mask for the date. DD refers to date, MON refers to the three-letter abbreviation for the month, and YY refers to the year. For example, the first day of the year would be written 01-JAN-03.