Oracle 8 native Data Types
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.
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.12345 NUMBER( 5, -3 ) 12000 98765 NUMBER( 5, -3 ) 99000 98765 NUMBER( 3 ) exception -
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 formatIf 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 calculationIt 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.




