Knowledge needed to get the field length defined by an Oracle database table and verify that the data is out of length

A problem with the recent project is the need to get the field length defined by the table in the Oracle database, and then verify that the data that will be entered into the database table exceeds the length of the field definition. So understand the type of Oracle storage string.

1, first understand the difference between varchar, varchar2, nvarchar, nvarchar2

This issue can be referenced by the blog http://www.cnblogs.com/lovewife/articles/2467663.html. Specifically as follows:

--varchar,varchar2

Contact:
1.varchar/varchar2 used to store variable-length strings
such as varchar (20), stored in the string ' ABC ', the field in the database is only 3 bytes, not 20 bytes
The maximum value for 2.size is 4000, and the minimum value is 1, and its value indicates the number of bytes, such as
varchar (20) indicates that the maximum number of 20 bytes of content can be stored

Difference:
1.varchar2 all characters are accounted for two bytes (in general), varchar only for Chinese characters and Full-width and other characters accounted for two bytes, numbers, English characters, etc. are a byte;
2.VARCHAR2 the null string equivalent to NULL processing, and varchar still in accordance with the empty string processing;
3.varchar2 characters need to be stored in several bytes, looking at the character set used by the database,
For example GBK, Chinese characters will occupy two bytes, English one
If it is UTF-8, the Chinese character generally occupies 3 bytes, English or 1.
But in general, we think it is two byte processing, because the Oracle installation time by default we all choose GBK encoding format, but we are in the page to do the input string length of the check, or the maximum length of the database design field except 3来 as the maximum-----to prevent the database from transplanting when the different encoding format;
For example: VARCHAR2 (10), in general, the maximum storage of 5 characters, 10 characters

--nvarchar,nvarchar2
Contact:
1.nvarchar/nvarchar2 used to store variable-length strings
The maximum value for 2.size is 4000, and the minimum value is 1, and its value represents the number of characters, not the number of bytes
3. These two types are more suitable for storing Chinese

Difference:
1.nvarchar characters in Chinese are generally in the 2 byte calculation, English numerals, etc. according to one's own calculation
All characters in 2.NVARCHAR2 are calculated according to 2 bytes;
3.nvarchar2 Although more space, but it has better compatibility, all recommended use;

Attention
VARCHAR2 is a specific data type provided by Oracle, Oracle can guarantee that the data type can be up and backward compatible in any version of VARCHAR2.
VarChar is not recommended for use in Oracle.

Specific to the difference between NVARCHAR2 and VARCHAR2, from the perspective of the use of the difference is: NVARCHAR2 in the calculation of length and character set-related, such as the database is the Chinese character set with length 10 for example, the

1, NVARCHAR2 (10) can be stored in 10 Chinese characters, if used to save English can only save 10 characters.

2, and VARCHAR2 (10), you can only deposit 5 Chinese characters, the English may save 10.

VARCHAR2 (size)

A variable-length string with a maximum length of size bytes, a maximum size of 4000, and a minimum of 1; You must specify a VARCHAR2 size;

NVARCHAR2 (size)

A variable-length string, based on the selected national character set, with a maximum length of size characters or bytes; The maximum size depends on the number of bytes required to store each character, with an upper limit of 4000; You must specify a NVARCHAR2 size;

Number (p,s)

A numerical value with a precision of p and a value range of S; the range of precision p is from 1 to 38; The range of the range S is from-84 to 127;
For example: Number (5,2) indicates that the integer part is the largest 3 digits, and the decimal part is 2 bits;
Number (5,-2) represents the integer part of the maximum of 7, where the reciprocal 2 digits of the integer are 0, and the preceding rounding.
Number means the default value, which is equivalent to number (5);

LONG

Variable-length character data with a length of up to 2G bytes;

DATE

Valid date range from January 1, 4712 to A.D. December 31, 4712

RAW (size)

Raw binary data of size byte, the maximum size is 2000 bytes; You must specify a size for raw;

LONG RAW

Raw binary data of variable length, which can reach up to 2G bytes;

CHAR (size)

Fixed-length character data whose length is size byte; The maximum size is 2000 bytes, while the minimum and default values are 1;

NCHAR (size)

is also a fixed length. Defined according to the Unicode standard

CLOB

A large object that can hold single-byte characters, a character set with unequal widths, and a maximum of 4G bytes

NCLOB

A large object that accommodates a single byte of character, does not support a character set of unequal widths, a maximum of 4G bytes, and stores a national character set

BLOB

A binary large object; maximum 4G bytes

BFILE

A locator that contains a large binary file that is stored outside the database, allowing the presence of an external LOB on the database server with character stream I/O, and a maximum size of 4G bytes.

Blob, CLOB, nclob three large objects (lobs), used to save large graphics files or formatted text files, such as Miceosoft Word documents, and audio, video, and other non-text files, the maximum length is 4GB. There are several types of lob, depending on the type of byte you use, and Oracle 8i actually stores the data inside the database. Special operations such as read, save, write, CLOB (Character Large Object) are used to store character data that corresponds to a database-defined character set. (similar to a long type) BLOB (Binary Large Object) can be used to store unstructured binary data. (similar to row and long row)

2, the type of storage string in oralce includes varchar varchar2 nvarchar2. Nvarchar is not a type of Oracle storage string

3, varchar varchar2 corresponding Java.sql.types.varchar

NVARCHAR2 corresponds to Java.sql.types.nvarchar

Other correspondence relations See blog http://blog.csdn.net/hbzyaxiu520/article/details/5457225. Specifically as follows:

Common database field types correspond to Java.sql.Types


Oracle and Java.sql.Types Correspondence


Oracle Java.sql.Types


Blob blob
Char Char
Clob CLOB
Date Date
Number decimal
Long varbinary
Nclob,nvarchar2 Other
smallint smallint
Timestamp Timstamp
Raw varbinary
VARCHAR2 varchar


SQL Server corresponds to Java.sql.Types


SQL Server Java.sql.Types
BigInt (2005,2008) bigint
Timstamp,binary binary
Bit bit
Char,nchar,unqualified Char
DateTime date
Money,smallmoney,decimal Decimal
Float (2005,2008) Double
Float (+) float
int integer
Image LongVarBinary
Text,ntext,xml LongVarChar


Numeric numeric
Real Real
smallint smallint
Datetime,smalldatetime Timestamp
tinyint tinyint
varbinary Varbinay
Nvarchar,varchar varchar


The correspondence of DB2 and Java.sql.Types


bigint bigint
Blob blob
Character,graphic Char
Clob CLOB
Date Date
Decimal decimal
Double Double
Integer integer
Longvargraphic LongVarChar
LongVarChar


Real Real
smallint smallint
Time Time
Timestamp timestamp
Vargraphic varchar
varchar


The correspondence of MySQL and Java.sql.Types


MySQL Java.sql.Types
bigint bigint
Tinyblob binary
Bit bit
Enum,set,char Char
Date,year Date
Decimal,numeric Decimal
Double,real Double
Mediumint,int integer
Blob,mediumblob blob
Longblob
Float Real


smallint smallint
Time Time
Timestamp,datetime Timestamp
tinyint tinyint
Varbinary,binary Varbinay
Varchar,tinytext,text varchar




The correspondence of Sybase and Java.sql.Types


Sybase Java.sql.Types
Binary binary
Bit bit
Char,nchar, Char
Money,smallmoney,decimal Decimal
Float double
int integer
Image LongVarBinary
Text LongVarChar
Numeric numeric
Real Real
smallint smallint
Datetime,smalldatetime Timestamp
tinyint tinyint
Varbinar,timestamp Varbinay
nvarchar,varchar, sysname varchar