Datatypes Used in Oracle 9i

Oracle Datatypes
These Oracle datatypes are as follows:
Character Strings
CHAR (size) – A fixed-sized field of characters. The largest this particular datatype can become is 2000 bytes. In other words, it can only hold 2000 characters. If you don’t specify the length of the CHAR datatype, the default size is a single character (i.e. 1 byte).
NCHAR (size) – A fixed-sized field of characters, where the character set is determined by its definition. So, the maximum size is 2000 bytes per row or 2000 characters. This handles multibyte character sets.
VARCHAR2 (size) – A variable-sized field of characters. The largest this datatype can become is 4000 characters.
NVARCHAR2 (size) – A variable-sized field of characters, where the character set is determined by its definition. The maximum size is 4000 bytes per row or 4000 characters. This handles multibyte character sets.
Note: The VARCHAR2 datatype is the successor of VARCHAR. So it is recommended that you use VARCHAR2 as a variable-sized array of characters.
LONG – A variable-sized field of characters. The maximum size of this field is 2GB.
Number
NUMBER (precision, scale) – A variable-sized number, where the precision is between 1 and 38 and size is between -84 and 127. A NUMBER datatype with only one parameter is NUMBER (precision), where the parameter specifies the precision of the number. A NUMBER datatype with no parameters is set to its maximum size.
Date and Time
DATE – A fixed-sized 7 bit field that is used to store dates. One thing to note is that the time is stored as part of the date. The default format DD-MON-YY can be overridden by NLS_DATE_FORMAT.
TIMESTAMP (precision) – A variable-sized value ranging from 7 to 11 bytes, that is used to represent a date/time value. It includes both date and time. The precision parameter determines how many numbers are in the fractional part of SECOND field. The precision of the SECOND field within the TIMESTAMP value may have a value ranging from 0 to 9 with a default precision of 6.
TIMESTAMP (precision) WITH TIME ZONE – A fixed-sized value of 13 bytes, which represents a date/time value along with a time zone setting. There are two ways one can set the time zone. The first is by using the UTC offset, say ‘+10:0’, or secondly by the region name, say ‘Australia/Sydney’.
TIMESTAMP (precision) WITH LOCAL TIME – A variable value ranging from 7 to 11 bytes. This particular datatype is similar to the TIMESTAMP WITH TIME ZONE datatype. The difference is that the data is normalised to the database time zone when stored. The entry is manipulated to concur with the client’s time zone when retrieved.
Intervals
INTERVAL DAY (day_precision) TO SECOND (second_precision) – A fixed-sized 11 byte value that represents a period of time. It includes days, hours, minutes and seconds.
INTERVAL YEAR (year_precision) TO MONTH - A fixed-sized 5 byte value that represents a period of time. It includes years and months.
Binaries
RAW (size) – A variable-sized field of raw binary data. The maximum size for this datatype is 2000 bytes.
LONG RAW - A variable-sized field of raw binary data. The maximum size for this datatype is 2 GB.
BLOB – The Binary Large Object is a field that holds unstructured binary data. The maximum size for this datatype is 4 GB.
CLOB – The Character Large Object is a field that holds single byte character data. The maximum size for this datatype is 4 GB.
NCLOB – The National Character Large Object is a field that holds either single byte of multibyte character data dependent on the national character set. The maximum size for this datatype is 4 GB.
BFILE – An external binary file. The maximum size for this file is 4 GB. The size is also limited by the operating system.
Rows
ROWID – A datatype that contains binary data that is used to identify a row. Each ROWID is:
6 bytes for normal indexes on non-partitioned tables, local indexes on partitioned tables and row pointers for chained/migrated rows. 10 bytes for global indexes on partitioned tables.
UROWID – The Universal ROWID is the datatype used to store both logical and physical ROWID values as well as foreign tables accessed through a gateway.
Alternatives for ANSI Standard Datatypes
Instead of using ANSI standard datatypes, you can use Oracle defined datatypes. View the table below to see the Oracle datatype alternative for ANSI standard datatypes.
ANSI Standard Oracle Datatype CHARACTER and CHAR CHAR CHARACTER VARYING and CHAR VARYING VARCHAR2 NUMERIC, DECIMAL, DEC, INTEGER, INT and SMALLINT NUMBER FLOAT, REAL, DOUBLE PRECISION FLOAT
Abstract Datatypes
In Oracle, one may create there own datatypes. Abstract datatypes allow Oracle to hold a range of datatypes. So, an abstract datatypes can have many parts to it. To do this one needs to create the datatype as an object. This object is made up of one or more datatypes.
Example of an Abstract Datatype
Let’s say that we want a datatype to split up a person’s address. The abstract datatype may be,
CREATE OR REPLACE TYPE persons_address AS OBJECT( v_streetNumber NUMBER, v_streetName VARCHAR2(30), v_citySuburb VARCHAR2(30), v_state VARCHAR2(4), v_postCode NUMBER);
When we create a table that references this abstract datatype the values must be inserted as
persons_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345)