Figure 3.7. Granting the
RESOURCE role to the
FLUGLE user with
Security Manager.

Oracle Datatypes

As a bridge to the lesson on Day 4, it's time to look at some basic facts about the datatypes available for use in an Oracle database. Every column in an Oracle database must be defined by one of these datatypes. With Oracle8, you can define your own datatype in addition to using a predefined datatype. The predefined datatypes for four categories of data are examined in the following sections:

• Numbers
• Strings
• Date and time information
• Large strings and BLOBs (Binary Large OBjects)

Numbers

Oracle offers several datatypes for storing numbers; each is suited for a different purpose.

The NUMBER datatype offers the greatest flexibility for storing numeric data. It accepts positive and negative integers and real numbers, and has from 1 to 38 digits of precision.

The syntax for specifying the NUMBER datatype when defining a column is

NUMBER (precision, scale)

The variables are defined as follows:

precision is the maximum number of digits to be stored

scale is used to indicate the position of the decimal point number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from _84 to 127.

Oracle can store from 1 to 38 digits of precision for a number. The number of bytes required to store a number in an Oracle database depends on how many digits are used to express the number.

If you limit the precision, Oracle limits the values that can be stored in the column to the defined precision. For instance, suppose you define a table named Number_Digits_Demo that contains two columns:

If you store a value in the Value column that exceeds its specified number of digits, Oracle returns an error as shown in Listing 3.1.

Listing 3.1. Oracle enforces numeric precision.

SQL> insert into number_digits_demo
  2  (record_no, value)
  3  values
  4  (101, 9999);
  1 row created.

SQL> insert into number_digits_demo
  2  (record_no, value)
  3  values
  4  (101, 10000);
(101, 10000)
      *
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column.

Take a look at a different example involving numeric precision and scale. Suppose you have a table named Scale_Precision_Demo with two columns:

In the number column, the precision is 6 and the scale is 2. In other words, out of a total of six digits of precision, Oracle reserves two digits to the right of the decimal point, leaving a maximum of four digits to the left of the decimal point. Also, the column cannot store more than six digits of precision for any number.

As Listing 3.2 illustrates, the number 1234.5 can be stored in the number column. However, the number 12345.1 can't be stored because it contains five digits to the left of the decimal point. Similarly, the number 12345, even though it has only five digits of precision, can't be stored in the column because it has five digits to the left of the decimal point.

Listing 3.2. Oracle enforces numeric precision and scale.

SQL> insert into scale_precision_demo
  2  (record_no, value)
  3  values
  4  (901, 1234.5);
1 row created.

SQL> insert into scale_precision_demo
  2  (record_no, value)
  3  values
  4  (901, 12345.1);
(901, 12345.1)
     *
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column

SQL> insert into scale_precision_demo
  2  (record_no, value)
  3  values
  4  (901, 12345);
(901, 12345)
     *
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column

Strings

To store strings, you can choose from several datatypes:

CHAR
VARCHAR
VARCHAR2
LONG

The bulk of many databases is character data; the advantages and disadvantages of each datatype are explained in the following paragraphs.

The CHAR datatype stores fixed-length character strings of up to 255 characters. If you don't specify a length, a CHAR column stores a single character.

You shouldn't use the VARCHAR datatype unless you have an older Oracle database that you're supporting. (Oracle warns that the VARCHAR datatype may not be supported in future releases.) The VARCHAR2 datatype was introduced with Oracle and is meant to replace VARCHAR. If you're building a new Oracle application, you won't have any reason to use the VARCHAR datatype.

VARCHAR2 stores up to 2,000 characters in a single column. If you absolutely must store more than that, you need to consider using the LONG datatype.

You can store 2GB of characters in a LONG column. However, you cannot use any of Oracle's built-in functions or operators with LONG columns. The best way to think of a LONG column is as a very large black box. You cannot search the contents of this box unless the box is emptied.

Follow these guidelines to choose an appropriate datatype for a column that will store character data.

• You can use CHAR to define columns that will store a single character.
• Use VARCHAR2 to store variable strings that contain up to 2,000 characters.
• Use LONG to store more than 2,000 characters in a column.

If you're trying to store more than 2,000 characters, don't create multiple VARCHAR2 columns to store the data. Assembling, searching, or manipulating the contents of multiple columns is impractical.

Using the CHAR Datatype

Because the CHAR datatype stores fixed-length columns, use it when you're defining columns that will contain a single character. Using the CHAR datatype to store larger strings isn't efficient because you will waste storage space.

Using the VARCHAR2 Datatype

Because it stores variable-length strings, the VARCHAR2 datatype is the preferred datatype for storing strings. This datatype can store up to 2,000 characters. However, the Oracle RDBMS is efficient; it will allocate only the storage that is needed to store each column value. On Day 7, "Taking Advantage of SQL Built-in Functions," you will analyze the many ways to use Oracle's built-in string functions and operators.

Date and Time Information

One of Oracle's strengths is its DATE datatype. The DATE datatype should really be named DATETIME because it provides storage for both date and time information. Oracle always allocates a fixed 7 bytes for a DATE column, even if you're using a DATE column to store date information only or time information only.