Oracle Basic data types (also called built-in data types built-in datatypes) can be categorized by type: String type, Number type, date type, LOB type, LONG raw& RAW type, ROWID & urowid type.
Before Apple told the string type, let's talk about encoding. Data of string types can be encoded into both the database character set (Char/varchar2/clob/long) and the international character set (NCHAR/NVARCHAR2/NCLOB). String data in a database is stored in a data block only after it is converted to a number by a character set (binary). With different encoding set conversions, even the same characters may be converted to different binary encodings. This is also the cause of garbled. The encoding format of a database is typically specified when the database is created. Of course, you can also modify the database encoding.
To view the data types that the database view contains:SELECT * from Dba_types WHERE the OWNER is NULL. details see oracle®database SQL Language Quick Refer ence 10/11G or official documents
A String type
The string data type can also be divided into fixed-length types (char/nchar) and variable-length types (VARCHAR2/NVARCHAR2) based on storage space.
Fixed length: means that although the input field value is less than the limit length of the field, the contents of the field value are stored in the data block when the data is actually stored and the space is automatically filled to the right. This approach is a waste of space, but storage efficiency is better than variable length types. It also reduces the migration of data rows to occur.
Variable length: means that when the input field value is less than the limit length of the field, the contents of the field value are stored directly in the data block without padding, which saves block space.
1.1:char type CHAR (size [BYTE | CHAR])
Char type, fixed-length string, is padded with spaces to reach its maximum length. A non-null char (12) always contains 12 bytes of information. The Char field can store up to 2,000 bytes of information. If you do not specify a char length when you create a table, the default is 1. In addition you can specify that it stores bytes or characters, such as char (Bytye) char (three char). In general, the default is to store bytes, you can view the database parameters
The value of the nls_length_semantics.
SQL Code
- SQL> show parameter nls_length_semantics;
- NAME TYPE VALUE
- ------------------ ----------- -----------------
- Nls_length_semantics string BYTE
- eg
- CREATE TABLE TEST
- (
- Name_old CHAR(Ten),
- Name_new Char(ten char)
- )
- INSERT into TEST
- ( name_old, name_new)
- SELECT ' Abcdefghij ' , ' you clear bytes with character ' from DUAL;
- COMMIT;
- INSERT into TEST
- ( name_old, name_new)
- SELECT ' You clear bytes with character ' , ' Abcdefghij ' from DUAL;
- ORA-12899: Column "SYS". " TEST "." Name_old "value is too large (actual value: 24, Maximum: 10)
Summary of Oracle BASIC data Types "Oracle"