SQL Datatypes

SQL Datatypes

• A data type defines a sort of value that a column should contain.
• In a database table, every column is necessary to have a name and data type.
• Data type may vary depending on the database.
• A datatype associates a fixed set of properties with the values that can be used in a column of a table or in an argument of a procedure or function. 
• These properties cause Oracle to treat values of one datatype differently from values of another datatype; 
• For example, Oracle can add values of NUMBER datatype but not values of RAW datatype.
• Oracle supplies the following built-in datatypes:
• Character datatypes
• CHAR
• CHAR (size)  
• Fixed-length character data of length size bytes.  
• Fixed for every row in the table (with trailing blanks); maximum size is 2000 bytes per row, the default size is 1 byte per row. Consider the character set (one-byte or multibyte) before setting size.
• NCHAR
• NCHAR(size)  
• Fixed-length character data of length size characters or bytes, depending on the national character set.  
• Fixed for every row in the table (with trailing blanks). Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 2000 bytes per row. Default is 1 character or 1 byte, depending on the character set.  
• VARCHAR2 and VARCHAR
• VARCHAR2 (size)  
• Variable-length character data.  
• Variable for each row, up to 4000 bytes per row. Consider the character set (one-byte or multibyte) before setting size. A maximum size must be specified. 
• NVARCHAR2
• NVARCHAR2 (size)  
• Variable-length character data of length size characters or bytes, depending on national character set. A maximum size must be specified.  
• Variable for each row. Column size is the number of characters for a fixed-width national character set or the number of bytes for a varying-width national character set. Maximum size is determined by the number of bytes required to store one character, with an upper limit of 4000 bytes per row. Default is 1 character or 1 byte, depending on the character set.  
• CLOB
• Single-byte character data.  
• Up to 2^32 - 1 bytes, or 4 gigabytes.  
• NCLOB
• Single-byte or fixed-length multibyte national character set (NCHAR) data. 
• Up to 2^32 - 1 bytes, or 4 gigabytes. 
• LONG
• LONG  
• Variable-length character data.  
• Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility. 
• NUMBER datatype
• NUMBER (p, s)  
• Variable-length numeric data. Maximum precision p and/or scale s is 38.  
• Variable for each row. The maximum space required for a given column is 21 bytes per row.
• DATE datatype
• Fixed-length date and time data, ranging from Jan. 1, 4712 B.C.E. to Dec. 31, 4712 C.E.  
• Fixed at 7 bytes for each row in the table. 
• The default format is a string (such as DD-MON-YY).
• Binary data types
• ROWID  
• Binary data representing row addresses.  
• Fixed at 10 bytes (extended ROWID) or 6 bytes (restricted ROWID) for each row in the table.
• BLOB
• Unstructured binary data.  
• Up to 2^32 - 1 bytes, or 4 gigabytes.
• BFILE
• Binary data stored in an external file.  
• Up to 2^32 - 1 bytes, or 4 gigabytes. 
• RAW
• RAW (size)  
• Variable-length raw binary data.  
• Variable for each row in the table, up to 2000 bytes per row. A maximum size must be specified. Provided for backward compatibility.  
• LONG RAW
• Variable-length raw binary data.  
• Variable for each row in the table, up to 2^31 - 1 bytes, or 2 gigabytes, per row. Provided for backward compatibility.