Datatypes

Using Datatypes

A datatype associates a fixed set of properties with values that are used in a column of a table or in an argument of a procedure or function. The properties of datatypes cause Oracle Database XE to treat values of one datatype differently from values of another datatype. For example, Oracle Database XE can use the addition operator on values of numeric datatypes, but not with values of some other datatypes.

The datatypes supported by Oracle Database Express Edition include:

■ Character datatypes

■ Numeric datatypes

■ Date and time (date-time) datatypes

■ Large Object (LOB) datatypes

When you create a table, you must specify a datatype for each of its columns to define the nature of the data to be stored in the column. For example, a column defined as a DATE datatype cannot accept the value February 29 (except for a leap year) or the values 2 or SHOE. When specifying a datatype, you can also indicate the longest value that can be placed in the column. In most cases, you only need columns of NUMBER, VARCHAR2, and DATE datatypes to create a definition of a table.

What Are the Character Datatypes?

You can use the following SQL datatypes to store character (alphanumeric) data:

■ The VARCHAR2 datatype stores variable-length character literals. When creating a VARCHAR2 column in a table, you must specify a string length between 1 and 4000 bytes for the VARCHAR2 column. Set the size to the maximum number of characters to be stored in the column. For example, a column to hold

the last name of employees can be restricted to 25 bytes by defining it as

VARCHAR2(25).

For each row, Oracle Database XE stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error. Using VARCHAR2 saves on space used by the table. For most cases where you need to store character data, you would use the

VARCHAR2 datatype.

■ The CHAR datatype stores fixed-length character literals. When creating a CHAR column in a table, you must specify a string length between 1 and 2000 bytes for the CHAR column. For each row, Oracle Database XE stores each value in the column as a fixed-length field. If the value of the character data is

less than specified length of the column, then the value is blank-padded to the fixed length. If a value is too large, Oracle Database XE returns an error.

■ NCHAR and NVARCHAR2 datatypes store only Unicode character data.

The NVARCHAR2 datatype stores variable-length Unicode character literals. The NCHAR datatype stores fixed-length Unicode character literals Choosing Between the Character Datatypes. When deciding which datatype to use for a column that will store character data in a table, consider the following:

■ Space usage

To store data more efficiently, use the VARCHAR2 datatype. The CHAR datatype adds blanks to maintain a fixed column length for all column values, whereas the VARCHAR2 datatype does not add extra blanks.

■ Comparison semantics

Use the CHAR datatype when trailing blanks are not important in string comparisons. Use the VARCHAR2 datatype when trailing blanks are important in string comparisons.

■ Future compatibility

The CHAR and VARCHAR2 datatypes are fully supported.

What Are the Numeric Datatypes?

The following SQL datatypes store numeric data:

■ NUMBER

■ BINARY_FLOAT

■ BINARY_DOUBLE

Use the NUMBER datatype to store integers and real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle database platforms. For nearly all cases where you need to store numeric data, you would use the NUMBER datatype. When defining numeric data, you can use the precision option to set the maximum number of digits in the number, and the scale option to define how many of the digits are to the right of the decimal point. For example, a field to hold the salary of an employee can be defined as NUMBER(8,2), providing 6 digits for the primary unit of currency (dollars, pounds,marks, and so on) and two digits for the secondary unit (cents, pennies, pfennigs, and so on).

Oracle Database XE provides the numeric BINARY_FLOAT and BINARY_DOUBLE datatypes exclusively for floating-point numbers. They support all of the basic functionality provided by the NUMBER datatype. However, while the NUMBER datatype uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE datatypes use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

Using the NUMBER Datatype

The NUMBER datatype stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle Database XE returns an error.

The NUMBER datatype can be specified with a precision (p) and a scale (s) designator. Precision is the total number of significant decimal digits, where the most significant digit is the left-most, nonzero digit, and the least significant digit is the right-most, known digit. Scale is the number of digits from the decimal point to the least.

You can specify a NUMBER datatype as follows:

■ NUMBER(p) for an integer

This represents a fixed-point number with precision p and scale 0, and is equivalent to NUMBER(p,0).

■ NUMBER(p, s) for a fixed-point number

This explicitly specifies the precision (p) and scale (s). It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle Database XE returns an error. If a value exceeds the scale, then Oracle Database XE rounds it.

■ NUMBER for a floating-point number

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

Storage of Scale and Precision

Actual Data Specified As Stored As 123.8915 NUMBER 123.8915 123.8915 NUMBER(3) 124

123.8915 NUMBER(4,1) 123.9

123.8915 NUMBER(5,2) 123.89

123.8915 NUMBER(6,3) 123.892

123.8915 NUMBER(7,4) 123.8915

1.238915e2 NUMBER(7,4) 123.8915

Using Floating-Point Number Formats

The BINARY_FLOAT and BINARY_DOUBLE datatypes store floating-point data in the 32-bit IEEE 754 format and the double precision 64-bit IEEE 754 format respectively.

Compared to the Oracle NUMBER datatype, arithmetic operations on floating-point data are usually faster for BINARY_FLOAT and BINARY_DOUBLE. High-precision values require less space when stored as BINARY_FLOAT and BINARY_DOUBLE datatypes.

The BINARY_FLOAT datatype has a maximum positive value equal to 3.40282E+38F and the minimum positive value equal to 1.17549E-38F. The BINARY_DOUBLE datatype has a maximum positive value equal to 

1.79769313486231E+308 and the minimum positive value equal to

2.22507485850720E-308.Storing Date and Time Data Oracle Database XE stores dates in its own internal format that corresponds to century, year, month, day, hour, minute, and second. For input and output of dates, the standard Oracle Database XE default date format is DD-MON-RR. The RR date-time format element enables you store 20th century dates in the 21st century by specifying only the last two digits of the year. Time is stored in a 24-hour format as HH24:MI:SS. Oracle Database Express Edition provides various SQL functions to calculate and convert date-time data.

Using DATE and TIMESTAMP Datatypes

Oracle Database supports the following date and time (date-time) datatypes:

■ DATE

■ TIMESTAMP

■ TIMESTAMP WITH TIME ZONE

■ TIMESTAMP WITH LOCAL TIME ZONE

DATE and TIMESTAMP Examples

Datatype Example

DATE 09-DEC-05

TIMESTAMP 09-DEC-05 02.05.49.000000 PM

TIMESTAMP WITH TIME ZONE 09-DEC-05 02.05.49.000000 PM -08:00

TIMESTAMP WITH LOCAL TIME ZONE 09-DEC-05 02.05.49.000000 PM

Using the DATE Datatype Use the DATE datatype to store point-in-time values (dates and times) in a table. For example, a column to hold the date that an employee is hired can by defined as a DATE datatype. An application that specifies the time for a job might also use the DATE datatype. For most cases where you need to store date data, you would use the DATE datatype. DATE columns are automatically formatted by Oracle Database XE to include a date and time component. The DATE datatype stores the century, year, month, day, hours, minutes, and seconds. The valid date range is from January 1, 4712 BC to December 31,

9999 AD. Although both the date and time are stored in a date column, by default, the date portion is automatically displayed for you, when retrieving date data. However, Oracle Database Express Edition enables you great flexibility in how you can display your dates and times.

Using the TIMESTAMP Datatype Use the TIMESTAMP datatype to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP. Using the TIMESTAMP WITH TIME ZONE Datatype Because the TIMESTAMP WITH TIME ZONE datatype can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions. Using the TIMESTAMP WITH LOCAL TIME ZONE Datatype Use the TIMESTAMP WITH LOCAL TIME ZONE datatype when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.

The TIMESTAMP WITH LOCAL TIME ZONE datatype is appropriate for two-tier applications in which you want to display dates and times that use the time zone of the client system. It is generally inappropriate in three-tier applications because data displayed in a Web browser is formatted according to the time zone of the Web server, not the time zone of the browser. The Web server is the database client, so its local time

is used. 

Storing Large Objects

Large Objects (LOBs) are a set of datatypes that are designed to hold large amounts of data. A LOB can hold up to a maximum size ranging from 8 terabytes to 128 terabytes depending on how your database is configured. Storing data in LOBs enables you to access and manipulate the data efficiently in your application.

The BLOB, CLOB, and NCLOB datatypes are internal LOB datatypes and are stored in the database. The BFILE datatype is the only external LOB datatype and is stored in an operating system file, outside the database.

Managing Tables

Tables are the basic unit of data storage in an Oracle database. They hold all user-accessible data. A table is a two-dimensional object made up of columns and rows. For example, the employees table includes (vertical) columns called employee_id, first_name, and last_name. Each (horizontal) row in the table contains a value for employee name and ID number. The most common type of table in an Oracle database is a relational table.