Oracle Database XE Datatypes: Character and Numeric
(Page 1 of 2 )
In this second part of a seven-part article series on Oracle Database XE, you'll learn about some of Oracle's built-in datatypes, such as character and numeric. This article is excerpted from chapter 30 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Understanding Oracle Datatypes
Assigning the correct datatype to the columns in your database tables is another key to your application’s success—its reliability, scalability, and so forth. This includes not only making sure you define a numeric datatype to a column when you know that the column will only have numeric values, but also enforcing the column’s relationship to a column from the same domain in other tables.
In the following sections, we provide an overview of the Oracle built-in datatypes available in every edition of Oracle, including Oracle Database XE. In addition to Oracle’s built-in datatypes, we introduce the ANSI-supported datatypes to help ease the transition from datatypes you may be familiar with in other relational databases.
Oracle’s built-in datatypes include character, numeric, datetime, large object, ROWID, and long/raw. For the most part, you will use character, numeric, and datetime datatypes in your applications. If you have applications with high-resolution photos or video that you want to store in the database, you will most likely use large object (LOB) datatypes, either the built-in CLOB(character large object) orBLOB(binary large object) datatypes or Oracle-supplied datatypes such asORDVideo,ORDAudio, andORDImage.
You will rarely use ROWID datatypes in your applications; they are primarily used internally in indexes and for very specialized applications. Similarly, you will most likely not use long, raw, or long/raw datatypes, as they are included in Oracle Database XE for backward-compatibility with applications written in previous versions of Oracle.
You store alphanumeric data in character datatypes, using either the database character set or the Unicode character set. You create your database with a specified character set. For example, in the United States you may use theUS7ASCII character set to support the English language and any other language that uses a subset of the English language. To expand your multilingual support in the database, you can create your database using theWE8ISO8859P1character set, which supports English and other European languages such as German, French, and Spanish. With the support for Unicode in PHP version 5.x, you can specifyAL16UTF16 to support character sets from all known written languages in your Web applications.
If you use multilingual character sets, you must be aware of the storage consequences for character data; depending on the character being stored in the column, Oracle may require anywhere from 1 to 4 bytes to store a given character. Therefore, when you define the length of your character strings, you must be cognizant of whether you are using byte semantics or character semantics. Byte semantics assumes a single-byte character set, where one character always requires one byte of storage. On the other hand, character semantics permits you to define the lengths of your character strings as the number of characters, regardless of how many bytes each character requires. Byte semantics is the default unless you override it using the initialization parameterNLS_LENGTH_SEMANTICS.
Note Even if you do not define a character set at database creation that is sufficient to support your multilingual needs, you can use the explicit Unicode datatypes:NCHAR,NVARCHAR, andNCLOB.
Table 30-1 lists the character datatypes and their qualifiers.
Table 30-1. Character Datatypes
Character Datatype Name
Fixed width character string that always stores size bytes when you specifyBYTE, or characters if you specifyCHAR, right-padded with spaces. Default size specification isBYTE unless you specifyCHAR.
Same asCHARbut uses Unicode; size is always in characters.
Variable width character string; stores only the number of bytes or characters assigned up to size bytes or characters. Default size specification isBYTEunless you specifyCHAR. The datatypeVARCHARis equivalent toVARCHAR2, but Oracle reserves the right to change the definition ofVARCHARin future releases.
Same asVARCHAR2, but uses Unicode; size is always in characters.
A deprecated character datatype included for backward-compatibility; Oracle may remove this datatype in future releases. Stores variable-length alphanumeric character strings up to 2GB in size. TheCLOBdatatype has all of the features ofLONGand none of the restrictions (such as only oneLONGcolumn per table); therefore, Oracle strongly recommends using theCLOBdatatype (discussed later in this chapter in the section “Large Object Datatypes”).
There are many situations where you should useNVARCHAR2to support your application—for example, if you want to create your table once for all of your worldwide branch offices. In this example, you create a table to contain a list of customers whose name and country code are stored in the database character set. The address, city, and postal code can be any address in the world, and as a result you need to ensure that it is stored using the Unicode character set: