PL/SQL User’s Guide and Reference phần 2 doc

Datatypes

2-20 PL/SQL User’s Guide and Reference

National Character Types

The widely used one-byte ASCII and EBCDIC character sets are adequate to

represent the Roman alphabet, but some Asian languages, such as Japanese, contain

thousands of characters. These languages require two or three bytes to represent

each character. How does Oracle deal with such dissimilar languages?

Oracle provides National Language Support (NLS), which lets you process

single-byte and multi-byte character data and convert between character sets. It also

lets your applications run in different language environments.

With NLS, number and date formats adapt automatically to the language

conventions specified for a user session. Thus, NLS allows users around the world

to interact with Oracle in their native languages.

PL/SQL supports two character sets called the database character set, which is used

for identifiers and source code, and the national character set, which is used for NLS

data. The datatypes NCHAR and NVARCHAR2 store character strings formed from the

national character set.

Note: When converting CHAR or VARCHAR2 data between databases with different

character sets, make sure the data consists of well-formed strings. For more

information, see Oracle8i National Language Support Guide.

NCHAR

You use the NCHAR datatype to store fixed-length (blank-padded if necessary)

national character data. How the data are represented internally depends on the

character set, which might use a fixed-width encoding such as US7ASCII or a

variable-width encoding such as JA16SJIS.

The NCHAR datatype takes an optional parameter that lets you specify a maximum

size up to 32767 bytes. The syntax follows:

NCHAR[(maximum_size)]

You cannot use a constant or variable to specify the maximum size; you must use an

integer literal in the range 1 .. 32767.

If you do not specify a maximum size, it defaults to 1. How you specify the

maximum size depends on the national character set. For fixed-width character sets,

you specify the maximum size in characters. For variable-width character sets, you

specify it in bytes. In the following example, the character set is JA16EUCFIXED,

which is fixed-width, so you specify the maximum size in characters:

my_string NCHAR(100); -- maximum size is 100 characters

Datatypes

Fundamentals 2-21

The maximum width of an NCHAR database column is 2000 bytes. So, you cannot

insert NCHAR values longer than 2000 bytes into an NCHAR column. Remember, for

fixed-width, multi-byte character sets, you cannot insert NCHAR values longer than

the number of characters that fit in 2000 bytes.

If the NCHAR value is shorter than the defined width of the NCHAR column, Oracle

blank-pads the value to the defined width. You cannot insert CHAR values into an

NCHAR column. Likewise, you cannot insert NCHAR values into a CHAR column.

NVARCHAR2

You use the NVARCHAR2 datatype to store variable-length national character data.

How the data is represented internally depends on the character set, which might

use a fixed-width encoding such as WE8EBCDIC37C or a variable-width encoding

such as JA16DBCS.

The NVARCHAR2 datatype takes a required parameter that specifies a maximum size

up to 32767 bytes. The syntax follows:

NVARCHAR2(maximum_size)

You cannot use a constant or variable to specify the maximum size; you must use an

integer literal in the range 1 .. 32767.

How you specify the maximum size depends on the national character set. For

fixed-width character sets, you specify the maximum size in characters. For

variable-width character sets, you specify it in bytes. In the following example, the

character set is JA16SJIS, which is variable-width, so you specify the maximum size

in bytes:

my_string NVARCHAR2(200); -- maximum size is 200 bytes

The maximum width of a NVARCHAR2 database column is 4000 bytes. Therefore,

you cannot insert NVARCHAR2 values longer than 4000 bytes into a NVARCHAR2

column. Remember, for fixed-width, multi-byte character sets, you cannot insert

NVARCHAR2 values longer than the number of characters that fit in 4000 bytes.

You cannot insert VARCHAR2 values into an NVARCHAR2 column. Likewise, you

cannot insert NVARCHAR2 values into a VARCHAR2 column.

Datatypes

2-22 PL/SQL User’s Guide and Reference

LOB Types

The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store

blocks of unstructured data (such as text, graphic images, video clips, and sound

waveforms) up to four gigabytes in size. And, they allow efficient, random,

piece-wise access to the data.

The LOB types differ from the LONG and LONG RAW types in several ways. For

example, LOBs (except NCLOB) can be attributes of an object type, but LONGs cannot.

The maximum size of a LOB is four gigabytes, but the maximum size of a LONG is

two gigabytes. Also, LOBs support random access to data, but LONGs support only

sequential access.

LOB types store lob locators, which point to large objects stored in an external file,

in-line (inside the row) or out-of-line (outside the row). Database columns of type

BLOB, CLOB, NCLOB, or BFILE store the locators. BLOB, CLOB, and NCLOB data is

stored in the database, in or outside the row. BFILE data is stored in operating

system files outside the database.

PL/SQL operates on LOBs through the locators. For example, when you select a

BLOB column value, only a locator is returned. If you got it during a transaction, the

LOB locator includes a transaction ID, so you cannot use it to update that LOB in

another transaction. Likewise, you cannot save a LOB locator during one session,

then use it in another session.

To manipulate LOBs, you use the supplied package DBMS_LOB. For more

information, see Oracle8i Application Developer’s Guide - Large Objects (LOBs).

BFILE

You use the BFILE datatype to store large binary objects in operating system files

outside the database. Every BFILE variable stores a file locator, which points to a

large binary file on the server. The locator includes a directory alias, which specifies

a full path name (logical path names are not supported).

BFILEs are read-only, so you cannot modify them. The size of a BFILE is system

dependent but cannot exceed four gigabytes (2**32 - 1 bytes). Your DBA makes sure

that a given BFILE exists and that Oracle has read permissions on it. The

underlying operating system maintains file integrity.

BFILEs do not participate in transactions, are not recoverable, and cannot be

replicated. The maximum number of open BFILEs is set by the Oracle initialization

parameter SESSION_MAX_OPEN_FILES, which is system dependent.

Datatypes

Fundamentals 2-23

BLOB

You use the BLOB datatype to store large binary objects in the database, in-line or

out-of-line. Every BLOB variable stores a locator, which points to a large binary

object. The size of a BLOB cannot exceed four gigabytes.

BLOBs participate fully in transactions, are recoverable, and can be replicated.

Changes made by package DBMS_LOB can be committed or rolled back. BLOB

locators can span transactions (for reads only), but they cannot span sessions.

CLOB

You use the CLOB datatype to store large blocks of character data in the database,

in-line or out-of-line. Both fixed-width and variable-width character sets are

supported. Every CLOB variable stores a locator, which points to a large block of

character data. The size of a CLOB cannot exceed four gigabytes.

CLOBs participate fully in transactions, are recoverable, and can be replicated.

Changes made by package DBMS_LOB can be committed or rolled back. CLOB

locators can span transactions (for reads only), but they cannot span sessions.

NCLOB

You use the NCLOB datatype to store large blocks of NCHAR data in the database,

in-line or out-of-line. Both fixed-width and variable-width character sets are

supported. Every NCLOB variable stores a locator, which points to a large block of

NCHAR data. The size of an NCLOB cannot exceed four gigabytes.

NCLOBs participate fully in transactions, are recoverable, and can be replicated.

Changes made by package DBMS_LOB can be committed or rolled back. NCLOB

locators can span transactions (for reads only), but they cannot span sessions.

Other Types

The following types allow you to store and manipulate logical values and

date/time values.

BOOLEAN

You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL

(which stands for a missing, unknown, or inapplicable value). Only logic operations

are allowed on BOOLEAN variables.

Datatypes

2-24 PL/SQL User’s Guide and Reference

The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and

NULL can be assigned to a BOOLEAN variable. You cannot insert the values TRUE

and FALSE into a database column. Also, you cannot select or fetch column values

into a BOOLEAN variable.

DATE

You use the DATE datatype to store fixed-length date/time values. DATE values

include the time of day in seconds since midnight. The date portion defaults to the

first day of the current month; the time portion defaults to midnight. The date

function SYSDATE returns the current date and time.

Valid dates range from January 1, 4712 BC to December 31, 9999 AD. A Julian date

is the number of days since January 1, 4712 BC. Julian dates allow continuous

dating from a common reference. You can use the date format model ’J’ with the

date functions TO_DATE and TO_CHAR to convert between DATE values and their

Julian equivalents.

In date expressions, PL/SQL automatically converts character values in the default

date format to DATE values. The default date format is set by the Oracle

initialization parameter NLS_DATE_FORMAT. For example, the default might be

’DD-MON-YY’, which includes a two-digit number for the day of the month, an

abbreviation of the month name, and the last two digits of the year.

You can add and subtract dates. For example, the following statement returns the

number of days since an employee was hired:

SELECT SYSDATE - hiredate INTO days_worked FROM emp

WHERE empno = 7499;

In arithmetic expressions, PL/SQL interprets integer literals as days. For instance,

SYSDATE + 1 is tomorrow.

For more information about date functions and format models, see Oracle8i SQL

Reference.