oracle Data Types

- character
– char: fixed length character string
– nchar: fixed length national character set character string
– nvarchar2: variable-length national character set character string
– varchar2: variable-length character string
– max length = 4000
– varchar: recommended to use varchar2 – synonymous with the VARCHAR2
- number
– number(9,2):
– 9 is the precision or total digits
– 2 is the scale or digits to the right of the decimal
– number(9): integer
– number: floating point number with decimal percision of 38
– float: also floating point number with decimal percision of 38
– float(9): floating point number with decimal percision of 9
- long: character string up to 2G
- date
– translate a number into words. Handy for writing out a check for an amount in words
– select to_char(to_date(112004,’J'), ‘JSP’) from dual;
- lob
– bfile
– blob
– clob
– nclob
- raw: data that is not to be interpreted by oracle
– raw
– long raw
- rowid
– restricted: Oracle7 rowids
– extended: Oracle8 and above row ids (extended for partitioning)
- urowid: universal rowids
– logical rowids for objects that are not physical or permanent or were not
generated by oracle
- SQL statements that create tables and clusters can also use ANSI datatypes and
datatypes from IBM’s products SQL/DS and DB2
- user defined datatypes
– object types
– REFs
– varrays
– nested tables