Oracle Datatypes

To store data in a database, an appropriate set of tables must first be created, with columns of the appropriate types defined. These are the basic Oracle datatypes we have to choose from:

String types
CHAR(length)	Fixed-length character data: max limit, 2,000
VARCHAR2(length)	Variable-length string: max limit: 4,000
Numeric
NUMBER	Float, 38-digit decimal significance: max scale, 127
NUMBER(precision)	Integer (fixed precision)
NUMBER(precision, scale)	Fixed point
Time and date
DATE	Date and time Jan 1, 4712 BCE Dec 31, 9999 CE; 1-second precision
TIMESTAMP	Extension of date fractional seconds with 0- to 9-digit precision, default 6

There are a few other types, particularly BLOB (Binary Large Objects) and CLOB (Character Large Objects), which are used to store arbitrary data. These types are opaque to the database meaning that the database cannot access and perform operations on the contents. BLOBs and CLOBs require special processing that is difficult to perform using SQL alone. As mentioned earlier, we'll look at these types in Chapter 9.

String Types

There are two basic types for storing string data, CHAR and VARCHAR2. The difference between them is that CHAR has a fixed length up to a limit of 2,000 bytes, whereas VARCHAR2 has a variable length up to 4,000 bytes. A fixed amount of space is always used for a CHAR entry; if the entry is shorter than the reserved length, it is padded with blanks. This can lead to surprising results when doing string comparisons, for example. A VARCHAR2 entry, however, will take only as much space as necessary. Except perhaps for small, fixed-length fields, such as identifiers, it's generally preferable to use VARCHAR2.

Strings in SQL statements are delimited with single quotes. If we wish to include a single quote as part of a string, we need to escape it that is, we need to precede it with a special character that indicates that it is to be treated as a literal single quote and not as a delimiter. To escape a single quote, we use another single quote. This is something like the backslash in Java: The backslash is the escape character, so in order to use it as a literal backslash, we have to precede it by another backslash. For example, if we want to use the string "Let's go" in an SQL statement, we write:

 'Let''s go' 

Numeric Types

The numeric types in Oracle are three variations on NUMBER. The default option, NUMBER, with no parameters, allows 38-digit precision, with a maximum scale of 127 (i.e., the decimal point can be moved right or left up to 127 places). The second option, NUMBER(precision), implies a scale of zero, so it is an integer with the specified precision. The third option, NUMBER(precision, scale), allows fixed-length (as specified by precision), floating point numbers, where the scale parameter specifies the minimum number of digits preceding the decimal point if positive, and the minimum number of digits following the decimal point if negative.

Because the default, NUMBER, is basically good enough to store any Java numeric type, the easiest thing to do is simply use NUMBER for everything. The drawback of not specifying realistic limits for precision and scale is that it can lead to inefficient storage of numbers and potentially poor performance in applications with large amounts of floating point data; in this case, it would be better to determine appropriate limits for precision and scale.

Although any Java numeric type can be stored in a NUMBER, the same is not true when we need to store a NUMBER in a Java type. We need to identify the type of data that the NUMBER is holding; this is usually obvious, and integer values are the most common. For NUMBERs known to be integer values, the safest choice in Java is the long type. For NUMBERs with a specified precision, we can choose int, if the precision is relatively small 9 or less; if the precision is larger, we need to use the long type.

For floating point values similar considerations apply. For NUMBERS known to be floating point values, the safest choice is the Java double type. For NUMBERs with a specified precision and scale, we can use the Java float type if the precision is 6 or less and the scale is not greater than 38 if either scale or precision are larger than these values, we need to use the double type.

Date Types

DATE is a bit misleading because it actually represents both date and time in Oracle SQL. There are two ways to consider a DATE, depending on whether we are using an interactive interface, such as SQL*Plus, or using it inside a host language, such as Java. In Java, the JDBC interface will perform the appropriate mapping to the Java Date class, so we can deal with it strictly in Java terms.

In SQL*Plus, we can generally use a string representation of the date, based on the default format (e.g., '12-July-02') or a format that we specify. We need to be aware that there is an underlying numerical representation because, depending on the format, when we perform a query we may see only part of the information that is actually stored. For example, a DATE field that stores the date and time July 12, 2001, 10:54:45 a.m. would display only the date portion by default: 12-JUL-02.

When we insert or change data in the database, we may inadvertently omit some of the information we intended. If we insert the value '12-JUL-2000', the time will be zero minutes, zero seconds after midnight on that date.

This behavior makes sense, but we need to be aware of it, because if we were to compare the values, they would not be equal, even though, by default, when we print them out they appear to be the same. If we wanted to know whether two values are the same date, without regard to the time, we need to truncate them before comparing them.

TIMESTAMP(precision), an extension of DATE, was introduced in Oracle 9i and is accurate to fractions of a second. DATE is accurate only to a second, which may not be sufficient for logging events or timing processes. The default precision of TIMESTAMP, 6 decimal places, means it is accurate to a microsecond. The maximum precision, 9, is accurate to a nanosecond. Note that this precision reflects only the ability of Oracle to store a value; using the operating system to obtain a time value, for example, is restricted to the accuracy to the resolution of the system clock, which is typically on the order of a millisecond.

Null

The value NULL is one of the most problematic features in relational databases, and we'll be revisiting it in different contexts as we expand our database experience. In short, it can be described as representing an unknown value. For example, when we enter information about our CDs into a database, we might not know the release date of a CD in which case, we could enter NULL.

NULL can cause several problems for the unwary. For example, comparing NULL with anything, including NULL, is always false. (This may remind you of the Not a Number [NaN] value in Java.) When NULL is used in arithmetic operations, the value of NULL propagates through the results. We'll explore this topic in more depth when we learn more about queries in the next chapter.