Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

4.1 Datatypes

The datatype
is one of the attributes for a
column
or a variable in a stored procedure. A datatype describes
and limits the type of information stored in a column, and limits the
operations that you can perform on columns.

You can divide Oracle datatype support into three basic varieties:
character datatypes, numeric datatypes, and datatypes that represent
other kinds of data. You can use any of these datatypes when you
create columns in a table, as with this SQL statement:

CREATE SAMPLE_TABLE(
char_field CHAR(10),
varchar_field VARCHAR2(10),
todays_date DATE)

You also use these datatypes when you define variables as part of a
PL/SQL procedure.

4.1.1 Character Datatypes

Character
datatypes can store any string value, including the string
representations of numeric values. Assigning a value larger than the
length specified or allowed for a character datatype results in a
runtime error. You can use string functions, such as UPPER, LOWER,
SUBSTR, and SOUNDEX, on standard (not large) character value types.

There are several different character datatypes:

CHAR

The CHAR
datatype stores character values with a fixed length. A CHAR datatype
can have between 1 and 2,000 characters. If you
don't explicitly specify a length for a CHAR, it
assumes the default length of 1. If you assign a value
that's shorter than the length specified for the
CHAR datatype, Oracle will automatically pad the value with blanks.
Some examples of CHAR values are:

CHAR(10) = "Rick      ", "Jon       ", "Stackowiak"

VARCHAR2

The VARCHAR2 datatype stores variable-length character strings.
Although you must assign a length to a VARCHAR2 datatype, this length
is the maximum length for a value rather than the required length.
Values assigned to a VARCHAR2 datatype aren't padded
with blanks. The VARCHAR2 datatype can have up to 4,000 characters.
Because of this, a VARCHAR2 datatype can require less storage space
than a CHAR datatype, because the VARCHAR2 datatype stores only the
characters assigned to the column.

At this time, the VARCHAR and VARCHAR2 datatypes are
synonymous in Oracle8 and later versions, but Oracle recommends the
use of VARCHAR2 because future changes may cause VARCHAR and VARCHAR2
to diverge. The values shown earlier for the CHAR values, if entered
as VARCHAR2 values, are:

VARCHAR2(10) = "Rick", "Jon", "Stackowiak"

NCHAR and NVARCHAR2

The NCHAR and
NVARCHAR2 datatypes store
fixed-length or variable-length character data using a different
character set from the one used by the rest of the database. When you
create a database, you specify the character set that will be used
for encoding the various characters stored in the database. You can
optionally specify a secondary character set as well (this is known
as the National Language
Set, or NLS). The secondary character set will
be used for NCHAR and NVARCHAR2 columns. For example, you may have a
description field in which you want to store Japanese characters
while the rest of the database uses English encoding. You would
specify a secondary character set that supports Japanese characters
when you create the database, and then use the NCHAR or NVARCHAR2
datatype for the columns in question.

With Oracle9i, you can
specify that you want to indicate the length of NCHAR and NVARCHAR2
columns in terms of characters, rather than bytes. This new feature
allows you to indicate, for example, that a column with one of these
datatypes is 7 characters long. The Oracle9i
database will automatically make the conversion to 14 bytes of
storage if the character set requires double byte storage.

Oracle Database 10g includes the Globalization Development Kit (GDK), which is designed to aid in the creation of Internet applications that will be used with different languages. The key feature of this kit is a framework that implements best practices for globalization for Java and PL/SQL developers. Oracle Database 10g also supports case- and accent-insensitive queries and sorts. You can use this feature if you want to use only base letters or only base letters and accents in a query or sort.

LONG

The LONG
datatype can hold up to 2 GB of character data. It is regarded as a
legacy datatype from earlier versions of Oracle. If you want to store
large amounts of character data, Oracle now recommends that you use
the CLOB and NCLOB datatypes. There are many restrictions on the use
of LONG datatypes in a table and within SQL statements, such as the
fact that you cannot use LONGs in WHERE, GROUP BY, ORDER BY, or
CONNECT BY clauses or in SQL statements with the DISTINCT qualifier.
You also cannot create an index on a LONG column.

CLOB and NCLOB

The CLOB and
NCLOB
datatypes can store up to 4 GB of character data prior to Oracle
Database 10g. With Oracle Database
10g, the limit has been increased to 128 TBs,
depending on the block size of the database. The NCLOB datatype
stores the NLS data. Oracle Database 10g
implicitly performs conversions between CLOBs and NCLOBs. For more
information on CLOBs and NCLOBs, please refer to the discussion about
large objects (LOBs) in "Other
Datatypes," later in this chapter.

4.1.2 Numeric Datatype

Oracle uses a standard,
variable-length internal format for storing numbers. This internal
format can maintain a precision of up to 38 digits.

The numeric
datatype for Oracle is NUMBER. Declaring a column or variable as
NUMBER will automatically provide a precision of 38 digits. The
NUMBER datatype can also accept two qualifiers, as in:

column NUMBER( precision, scale )

The precision of the datatype
is the total number of significant digits in the number. You can
designate a precision for a number as any number of digits up to 38.
If no value is declared for precision, Oracle
will use a precision of 38. The scale
represents the number of digits to the right of the decimal point. If
no scale is specified, Oracle will use a scale of 0.

If you assign a negative number to the scale,
Oracle will round the number up to the designated place to the
left of the decimal point. For example, the
following code snippet:

column_round NUMBER(10,-2)
column_round = 1,234,567

will give column_round a value of 1,234,600.

The NUMBER datatype is the only datatype that stores numeric values
in Oracle. The ANSI datatypes of DECIMAL, NUMBER,
INTEGER, INT, SMALLINT, FLOAT, DOUBLE PRECISION, and REAL are all
stored in the NUMBER datatype. The language or product
you're using to access Oracle data may support these
datatypes, but they're all stored in a NUMBER
datatype column.

4.1.3 Date Datatype

As
with the NUMERIC datatype, Oracle stores all dates and times in a
standard internal format. The standard Oracle date format for input
takes the form of DD- MON-YY HH:MI:SS, where DD represents up to two
digits for the day of the month, MON is a three-character
abbreviation for the month, YY is a two-digit representation of the
year, and HH, MI, and SS are two-digit representations of hours,
minutes, and seconds, respectively. If you don't
specify any time values, their default values are all zeros in the
internal storage.

You can change the format you use for inserting dates for an instance
by changing the NLS_DATE_FORMAT parameter for the instance. You can
do this for a session by using the ALTER SESSION SQL statement or for
a specific value by using parameters with the TO_DATE expression in
your SQL statement.

Oracle SQL supports date arithmetic in which integers represent days
and fractions represent the fractional component represented by
hours, minutes, and seconds. For example, adding .5 to a date value
results in a date and time combination 12 hours later than the
initial value. Some examples of date arithmetic are:

12-DEC-99 + 10 = 22-DEC-99
31-DEC-1999:23:59:59 + .25 = 1-JAN-2000:5:59:59

As of Oracle9i, Release 2, Oracle also supports
two INTERVAL datatypes, INTERVAL YEAR TO
MONTH and INTERVAL DAY TO SECOND, which are used for storing a
specific amount of time. This data can be used for date arithmetic.

4.1.4 Other Datatypes

Aside from the basic character, number, and date datatypes, Oracle
supports a number of specialized datatypes:

RAW and LONG RAW

Normally, your Oracle database not only
stores data but also interprets it. When data is requested or
exported from the database, the Oracle database sometimes massages
the requested data. For instance, when you dump the values from a
NUMBER column, the values written to the dump file are the
representations of the numbers, not the internally stored numbers.

The RAW and LONG RAW datatypes circumvent any interpretation on the
part of the Oracle database. When you specify one of these datatypes,
Oracle will store the data as the exact series of bits presented to
it. The RAW datatypes typically store objects with their own internal
format, such as bitmaps. A RAW datatype can hold 2 KB, while a LONG
RAW datatype can hold 2 GB.

ROWID

The
ROWID is
a special type of column known as a
pseudocolumn.
The ROWID pseudocolumn can be accessed just like a column in a SQL
SELECT statement. There is a ROWID pseudocolumn for every row in an
Oracle database. The ROWID represents the specific address of a
particular row. The ROWID pseudocolumn is defined with a ROWID
datatype.

The ROWID relates to a specific location on a disk drive. Because of
this, the ROWID is the fastest way to retrieve an individual row.
However, the ROWID for a row can change as the result of dumping and
reloading the database. For this reason, we don't
recommend using the value for the ROWID pseudocolumn across
transaction lines. For example, there is no reason to store a
reference to the ROWID of a row once you've finished
using the row in your current application.

You cannot set the value of the standard ROWID pseudocolumn with any
SQL statement.

The format of the ROWID pseudocolumn changed with Oracle8. Beginning
with Oracle8, the ROWID includes an identifier that points to the
database object number in addition to the identifiers that point to
the datafile, block, and row. You can parse the value returned from
the ROWID pseudocolumn to understand the physical storage of rows in
your Oracle database.

You can define a column or variable with a ROWID datatype, but Oracle
doesn't guarantee that any value placed in this
column or variable is a valid ROWID.

ORA_ROWSCN

Oracle Database 10g supports a new pseudocolumn,
ORA_ROWSCN, which holds the System
Change Number (SCN) of the last transaction that modified the row.
You can use this pseudocolumn to check easily for changes in the row
since a transaction started. For more information on SCNs, see the
discussion of concurrency in Chapter 7.

LOB

A LOB, or large object datatype,
can store up to 4 GB of information. LOBs come in three varieties:

CLOB, which
can only store character data

NCLOB, which
stores National Language character set data

BLOB, which
stores data as binary information

You can designate that a LOB should store its data within the Oracle
database or that it should point to an external file that contains
the data.

LOBs can participate in transactions. Selecting a LOB datatype from
Oracle will return a pointer to the LOB. You must use either the
DBMS_LOB PL/SQL built-in package or the OCI interface to actually
manipulate the data in a LOB.

To facilitate the conversion of LONG datatypes to LOBs,
Oracle9i
includes support for LOBs in most functions that support LONGs, as
well as a new option to the ALTER TABLE statement that allows the
automatic migration of LONG datatypes to LOBs.

BFILE

The
BFILE datatype acts as a pointer to a
file stored outside of the Oracle database. Because of this fact,
columns or variables with BFILE datatypes don't
participate in transactions, and the data stored in these columns is
available only for reading. The file size limitations of the
underlying operating system limit the amount of data in a BFILE.

XMLType

As part of its support for XML, Oracle9i
includes a new datatype called
XMLType. A column defined as this type
of data will store an XML document in a character LOB column. There
are built-in functions that allow you to extract individual nodes
from the document, and you can also build indexes on any particular
node in the XMLType document.

User-defined data

Oracle8 and later versions allow
users to define their own complex datatypes, which are created as
combinations of the basic Oracle datatypes previously discussed.
These versions of Oracle also allow users to create objects composed
of both basic datatypes and user-defined datatypes. For more
information about objects within Oracle, see Chapter 13.

AnyType, AnyData, AnyDataSet

Oracle9i and
newer releases include
three new datatypes that
can be used to explicitly define data structures that exist outside
the realm of existing datatypes. Each of these datatypes must be
defined with program units that let Oracle know how to process any
specific implementation of these types.

4.1.5 Type Conversion

Oracle automatically converts some
datatypes to other datatypes, depending on the SQL syntax in which
the value occurs.

When you assign a character value to a numeric datatype, Oracle
performs an implicit conversion of the ASCII value represented by the
character string into a number. For instance, assigning a character
value such as 10 to a NUMBER column results in an automatic data
conversion.

If you attempt to assign an alphabetic value to a numeric datatype,
you will end up with an unexpected (and invalid) numeric value, so
you should make sure that you're assigning values
appropriately.

You can also perform explicit conversions on data, using a variety of
conversion functions available with Oracle. Explicit data conversions
are better to use if a conversion is anticipated, because they
document the conversion and avoid the possibility of going unnoticed,
as implicit conversions sometimes do.

4.1.6 Concatenation and Comparisons

The concatenation operator for
Oracle SQL on most platforms is two vertical lines (||).
Concatenation is performed with two character values.
Oracle's automatic type conversion allows you to
seemingly concatenate two numeric values. If NUM1 is a numeric column
with a value of 1, NUM2 is a numeric column with a value of 2, and
NUM3 is a numeric column with a value of 3, the following expressions
are TRUE:

NUM1 || NUM2 || NUM3 = "123"
NUM1 || NUM2 + NUM3 = "15" (12 + 3)
NUM1 + NUM2 || NUM3 = "33" (1+ 2 || 3)

The result for each of these expressions is a character string, but
that character string can be automatically converted back to a
numeric column for further calculations.

Comparisons between values of the same
datatype work as you would expect. For example, a date that occurs
later in time is larger than an earlier date, and 0 or any positive
number is larger than any negative number. You can use relational
operators to compare numeric values or date values. For character
values, comparisons of single characters are based on the underlying
code pages for the characters. For multicharacter strings,
comparisons are made until the first character that differs between
the two strings appears. The result of the comparison between these
two characters is the result of the overall comparison.

If two character strings of different lengths are compared, Oracle
uses two different types of comparison semantics:
blank-padded
comparisons and non-padded
comparisons. For a blank-padded comparison, the
shorter string is padded with blanks and the comparison operates as
previously described. For nonpadded comparisons, if both strings are
identical for the length of the shorter string, the shorter string is
identified as smaller. For example, in a blank-padded comparison the
string "A " (a capital A followed
by a blank) and the string "A" (a
capital A by itself) would be seen as equal, because the second value
would be padded with a blank. In a nonpadded comparison, the second
string would be identified as smaller because it is shorter than the
first string. Nonpadded comparisons are used for comparisons in which
one or both of the values are VARCHAR2 or NVARCHAR2 datatypes, while
blank-padded comparisons are used when neither of the values is one
of these datatypes.

Oracle Database
10g includes a feature called the Expression
Filter, which allows you to store a complex comparison expression as
part of a row. You can use the EVALUATE function to limit queries
based on the evaluation of the expression.

4.1.7 NULLs

The NULL value is one of the key features of
the relational database. The NULL, in fact, doesn't
represent any value at allit represents the lack of a value.
When you create a column for a table that must have a value, you
specify it as NOT NULL, meaning that it cannot contain a NULL value.
If you try to write a row to a database table that
doesn't assign a value to a NOT NULL column, Oracle
will return an error.

You can assign NULL as a value for any datatype. The NULL value
introduces what is called three-state
logic to your SQL operators. A normal
comparison has only two states: TRUE or FALSE. If
you're making a comparison that involves a NULL
value, there are three logical states: TRUE, FALSE, and none of the
above.

None of the following conditions are true for Column A if the column
contains a NULL value:

A > 0 A < 0 A = 0 A != 0

The existence of three-state logic can be confusing for end users,
but your data may frequently require you to allow for NULL values for
columns or variables.

You have to test for the presence of a NULL value with the relational
operator IS NULL, because a NULL value is not equal to 0 or any other
value. Even the expression:

NULL = NULL

will always evaluate to FALSE, because a NULL value
doesn't equal any other value.