PL/SQL Programming   «Prev  Next»

PL/SQL Datatypes

The correct values and their datatypes are:
  1. TRUE: SCALAR datatype
  2. RECORD: COMPOSITE datatype
  3. %ROWTYPE: REFERENCE datatype
  4. BLOB: LOB datatype


There are two generalized types that you define in packages. You can declare
  1. static or
  2. 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
  1. integer,
  2. floating point,
  3. character,
  5. date,
  6. collection,
  7. reference, and
  8. 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.