Oracle 8 native Data Types  

The Oracle 8 datatypes are the native building blocks of the database. With these parts, the developer can define the structure of the tables.

CHAR and NCHAR

The CHAR type stores fixed length character data up to 2000 characters. Unused space in the character string is padded with spaces. But however, if you set a longer value with trailing blanks, Oracle will strip the blanks until the value fits into the field. If the value is to large for the field Oracle returns an error.
NCHAR is the National Language Support version of CHAR.

VARCHAR, VARCHAR2, NVARCHAR and NVARCHAR2

The VARCHAR types are effective to store variable-length character strings. A VARCHAR will store up to a maximum of 4000 characters. This type only needs as much storage depending on the data value.
At this moment VARCHAR is a synonym for VARCHAR2. Oracle does not guarantee that the comparison semantics for VARCHAR will be the same in future versions. So VARCHAR2 is the preferred datatype for variable character data.

LONG, RAW and LONG RAW

LONGs store character data that are converted when moved from one database to the other. LONGs can store up to 2 gigabytes. RAWs are used to store data that won't be converted by Oracle. RAW can be up to 2000 bytes and LONG RAW up to 2 gigabytes
LONGs are only supported for backward compatibility. Use CLOB or NCLOB for character data and BLOB for binary data.

CLOB, BLOB and NCLOB

These are the new multimedia types. These types will store up to 4 GB data. NCLOB stored NLS character data.

BFILE

The BFILE type is to store a pointer to an external file. The BFILE is read only and can store up to 4 gigabytes. See also the DBMS_LOB package for manipulation.

NUMBER

The NUMBER datatype stores all Oracle numeric data. A number can store up to 38 significant digits. A number may hold an integer of a floating point value. A floating point value is in the range 1 x 10^-130 to 9.99..9 x 10^125 and from -1 x 10^-130 to 9.99..99 x 10^125. But remember how large or small the numbers are, it can only hold 38 digits of precision.
Numbers may be defined with a precision and a scale. The precision is the maximum number of significant digits the number will hold. The scale defines the number of decimals. The scale may also be a negative value. In that case, the number will have no decimals but it will round up as in the example below.

12345	NUMBER( 5, -3 )	12000
98765	NUMBER( 5, -3 )	99000
98765	NUMBER( 3 )	exception

When no precision or scale is defined, the column will store any value. If the number of digits is more than the defined precision, an exception will be raised.

DATE

The DATA type stores date and time information. It stores the Year, Month, Day, Hours, Minutes and seconds. It has no problems with the Year2000 although some applications may use the format DD-MM-YY and assume a 1900 prefix when entering data.
The RR format specifier in TO_CHAR and TO_DATE functions allow to change to different centuries depending on the year. The years 50-99 will be 19xx and 00-49 will be 20xx. A global setting on the server can help migrating older applications. Note that this will also be true in the year 2000!

SQL> SELECT TO_CHAR( TO_DATE( '01/01/49', 'dd/mm/rr' )
                    ,'dd-mm-yyyy' ) Before50
           ,TO_CHAR( TO_DATE( '01/01/99', 'dd/mm/rr' )
                    ,'dd-mm-yyyy' ) After50
           ,TO_CHAR( SYSDATE, 'dd-mm-yyyy' ) Today
       FROM DUAL;

BEFORE50   AFTER50    TODAY
---------- ---------- ----------
01-01-2049 01-01-1999 16-03-1998

-- Switch computer date to 2001
BEFORE     AFTER      TODAY
---------- ---------- ----------
01-01-2049 01-01-1999 08-04-2001
SQL>

Figure 1: How to use the RR format

If no time is defined in a date column, Oracle will take 12:00:00 am as a default. It is possible to calculate with date values. The value 1 will be one day, so 10 days or 1 hour from now may be derived from the database as:

SQL> SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH:MI') "Now"
           ,SYSDATE + 10 "10 Days"
           ,TO_CHAR(SYSDATE + 1/24,'HH:MI  ') "+1 hour"
       FROM DUAL;

Now              10 Days   +1 hour
---------------- --------- -------
14/04/1998 09:43 24-APR-98 10:43

SQL> 

Figure 2: Date calculation

It is also possible to subtract two dates from each other. The resulting number will be the time that has elapsed between the two dates in days.

SQL> SELECT EName, HireDate
           ,TO_CHAR((SYSDATE - HireDate)/365,'99.9') "Years"
       FROM EMP;

ENAME      HIREDATE  Years
---------- --------- -----
SMITH      17-DEC-80  17.3
ALLEN      20-FEB-81  17.2
WARD       22-FEB-81  17.2
JONES      02-APR-81  17.0
  :            :       :

Figure 3: Years employed

ROWID

The ROWID datatype is used to store the address of every record in the database. The field is read-only and can be used to identify the physical position of the record in the database or to access a particular row. The the ROWID is known it is the fastest way of access to a particular row.
This datatype has changed from Oracle7 and is now called the extended rowid. The Oracle7 type is called restricted rowid and maintained for backward compatibility.
An extended rowid has the following format OOOOOOFFFBBBBBBRRR (base64 encoding):

OOOOOO	The data object number
FFF	The datafile
BBBBBB	The data block
RRR	The row in the given datablock

An restricted rowid has the following format block.row.file (binary presentation):

block	The data block in the given file
row	The row in the given datablock
file	The data file

The given object_id and datafile number can be queried from the dictionary views dba_objects and dba_data_files See also The DBMS_ROWID Package for ROWID manipulation in PL/SQL code.

MLSLABEL

Trusted Oracle provides an extra datatype to store labels from a multilevel secure system. When you create a table in trusted Oracle a field ROWLABEL is added to store the user label for each record.
You may define MLSLABEL in non trusted oracle for compatibility with Trusted Oracle. But the field may only store NULL.

National Language Support: The NLS versions of CHAR, VARCHAR, VARCHAR2 and CLOB allow to store character data for different languages. These version may need more bytes for a single character representation.