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