Database Reference
In-Depth Information
Datatypes” section in Chapter 2 of the manual and lists 18 different type codes for the 21 data
types implemented in Oracle9
i
(the national character set types
NCHAR
,
NVARCHAR2
and
NCLOB
use
the same type code as
CHAR
,
VARCHAR2
, and
CLOB
respectively). The most common data type
codes and names are summarized in Table 24-12.
Table 24-12.
Data Type Codes vs. Names
Data Type Code
Data Type Name
VARCHAR2
,
NVARCHAR2
1
NUMBER
2
DATE
12
CHAR
,
NCHAR
96
CLOB
,
NCLOB
112
BLOB
113
TIMESTAMP
180
The built-in SQL function
DUMP(
expr
[,
format
[,
position
[,
length
]]])
may be used to
display the data type code of a column along with the internal representation of the column's
value. The first argument to
DUMP
is the format for displaying the internal representation. The
formats supported are octal (
format
=8), decimal (default or
format
=10), hexadecimal (
format
=16),
and individual single-byte characters (
format
=17). Information on the character set is included,
if 1000 is added to one of the three aforementioned format settings (
format
=
format
+1000). When
position
and
length
are omitted, the entire internal representation is returned. Otherwise, merely
the portion starting at offset
position
having
length
bytes is considered. Here's an example:
SQL> SELECT dump(employee_id, 16, 1, 1) empid_dmp,
dump(last_name, 1017,1,2) name_dmp, dump(hire_date, 10, 1, 1) date_dmp
FROM hr.employees
WHERE rownum=1;
EMPID_DMP NAME_DMP DATE_DMP
--------------- ------------------------------------------ -----------------
Typ=2 Len=3: c2 Typ=1 Len=8 CharacterSet=WE8MSWIN1252: O,C Typ=12 Len=7: 119
As one might expect, the values of
Typ
in the example correspond with the data type codes
in Table 24-12.
Bind in Oracle10
g
and Oracle11
g
Oracle10
g
and Oracle11
g
Bind#
n
sections are different from an Oracle9
i
Bind#
n
section. Oracle10
g
and Oracle11
g
use 17 parameters to convey detailed information on bind variables. There is
currently no information on three of the new parameters. Some of the parameters that were
also present in Oracle9
i
have been renamed. Taking this into account, Table 24-13 presents the
available information.
