Lets discuss data types used with PL/SQL. These types should not be
confused with database types. In most cases, capabilities and limitations between
database and PL/SQL types are identical, but some have dramatically different
storage capabilities that can pop up to bite you.
PL/SQL data types can be broken down into the following categories:
¦ Scalar
¦ Reference
¦ Composite
¦ LOB
The Scalar category is broken down further into subcategories, or families of types.
The next few sections discuss each category, subcategory, and PL/SQL data type.
Scalar
A Scalartype is a data type that holds a single value. Scalar types can be broken
down into subcategories, or families, that include
¦ Character/String
¦ Number
¦ Boolean
¦ Date/Time
We’ll review each of these in detail in the following sections
.
Character/String
PL/SQL character or string types include everything from single character values to
large strings up to 32K in size. These types can store letters, numbers, and binary
data, and they can store any character supported by the database character set.
They all define their precision as an integer with units in bytes (the default setting of
bytes can be changed, as you will see in the next section on “Character Semantics”)
at the time the variable is declared.
Character Semantics
Character/stringtypes define precision, or storage, with an integer. The number
provided actually specifies the number of bytes allowed rather than the number of
characters. Prior to Oracle 9i, this was a real problem when working with multibyte
characters. It was possible that a variable precision of 2 could not even handle a
single three-byte Asian character. Oracle introduced character semantics in 9i to
solve this problem.
Character semantics can be specified for the system using the NLS_LENGTH_
SEMANTICS init.ora parameter, or for each variable in the declaration. The
following example shows a normal declaration of a variable of type VARCHAR2:
DECLARE
v_string VARCHAR2(10);
By default, this declaration means that the variable v_string can store up to ten
bytes. Here we modified the declaration to use character semantics:
5pt; COLOR: #231f20; FONT-FAMILY: Courier; mso-bidi-font-family: Courier">DECLARE v_string VARCHAR2(10 CHAR);
The addition of the CHAR to the precision means that the v_string variable will
now store up to ten characters, regardless of the number of bytes per character.
Character/String Types
Type Description
CHAR Fixed-length character data type. The precision is specified as
an integer. Storage is in bytes rather than characters by default.
Use character semantics to override.
LONG The LONG PL/SQL type is different than the LONG database type.
It is a variable-length type with a limit of 32K (32,760 bytes). It
is possible for a column of type LONG to not fit in a variable of
type LONG. Because of the difference between the PL/SQL and
database types, use of the LONG PL/SQL type is limited.
LONG RAW LONG RAW holds binary data up to 32K (32,760 bytes). Just
like the LONG type, the LONG RAW differs in size restriction
between PL/SQL type and database type. It is possible for a
column of type LONG RAW to not fit in a variable of type
LONG RAW. Because of the difference between the PL/SQL and
database types, use of the LONG RAW PL/SQL type is limited.
Type Description
NCHAR NCHAR holds fixed-length national character data. It is identical
to the CHAR type but takes on the character set specified by the
National Character Set.
NVARCHAR2 NVARCHAR2 holds variable-length character data. It is identical
to the VARCHAR2 type, but takes on the character set specified
by the National Character Set.
RAW The RAW type stores fixed-length binary data and can hold up to
32K (32,760 bytes). The RAW type for the database can hold only
2K, so the problem is the opposite of that experienced with the
LONG and LONG RAW types. If a RAW variable holds more than
2K, it cannot insert it into the database column of the same type.
ROWID Every record in a table contains a unique binary value called a
ROWID. The rowid is an identifier of the row in the table. The
PL/SQL type is the same and stores the ROWID of a database
record without conversion to a character type. The ROWID
type supports physical rowids, but not logical rowids.
UROWID UROWID supports both physical and logical rowids. Oracle
recommends using the UROWID PL/SQL type when possible.
VARCHAR VARCHAR is an ANSI-standard SQL type, synonymous with
VARCHAR2. Oracle recommends VARCHAR2 be used to
protect against future modifications to VARCHAR impacting code.
VARCHAR2 The VARCHAR2 PL/SQL data type can store up to 32K (32,767)
bytes in Oracle 10 g. The database VARCHAR2 type can store
only 4K. This can be a problem, of course, when a variable of
type VARCHAR2 holds data that exceeds 4K and attempts to
insert the contents into a database column of type VARCHAR2.
IT Articles 2007
