PL/SQL Datatypes
The correct values and their datatypes are:
TRUE
: SCALAR datatype
RECORD
: COMPOSITE datatype
%ROWTYPE
: REFERENCE datatype
BLOB
: LOB datatype
Types
There are two generalized types that you define in packages. You can declare
- static or
- dynamic datatypes.
Datatypes are typically PL/SQL structures, collections, reference cursors, and cursors. All of these can be dynamic or static datatypes.
They are dynamic when their declaration anchors their type to a row or column definition. You use the %ROWTYPE to anchor to a row and %TYPE to anchor to a column, as qualified in Table 2-4. Types are static when they rely on explicitly declared SQL datatypes, such as DATE, INTEGER, NUMBER, or VARCHAR2. As a general rule, package specifications are independent of other schema-level objects.
You build dependencies when you anchor package specification–declared types to catalog objects, like tables and views. If something changes in the dependent table or view, the package specification becomes invalid. Changes in
package specifications can create a cascade reaction that invalidates numerous package bodies and standalone schema-level programs.
Attribute |
Description |
%ROWTYPE |
The %ROWTYPE anchors the datatype of a variable to the row structure of a database catalog object (table or view),
or PL/SQL record structure. The new variable inherits both the position and datatype of the columns found in the
referenced table or view when you anchor to a catalog object. The new variable inherits both the position and
datatype of the explicit PL/SQL record structure, which may inherit indirectly from one or more catalog objects |
%TYPE |
The %TYPE anchors the datatype of a variable to a column
datatype found in a database catalog object, like a table
or view. |
Beginning Oracle PL/SQL
Table 2-4: Anchoring Attributes
BLOB Datatype
The BLOB datatype stores unstructured binary large objects.
BLOB objects can be thought of as bitstreams with no character set semantics.
BLOB objects can store binary data up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).
If the tablespaces in your database are of standard block size, and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column, then this is equivalent to (4 gigabytes - 1) * (database block size). BLOB objects have full transactional support. Changes made through SQL, the DBMS_LOB package, or the Oracle Call Interface (OCI) participate fully in the transaction. BLOB value manipulations can be committed and rolled back. However, you cannot save a BLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.
Every constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values.
PL/SQL provides many predefined datatypes. For instance, you can choose from
- integer,
- floating point,
- character,
- BOOLEAN,
- date,
- collection,
- reference, and
- large object (LOB) types.
PL/SQL also lets you define your own subtypes. This chapter covers the basic types used frequently in PL/SQL programs. Later chapters cover the more specialized types.