Oracle PL/SQL for DBAs

Types of PL/SQL Datatypes

Whenever you declare a variable or a constant, you must assign it a datatype. (PL/SQL is, with very few exceptions, a strongly typed language.) PL/SQL offers a comprehensive set of predefined scalar and composite datatypes , and you can create your own user-defined types (also known as abstract datatypes ).

Virtually all of these predefined datatypes are defined in the PL/SQL STANDARD package. Here, for example, are the statements that define the Boolean datatype and two of the numeric datatypes:

    CREATE OR REPLACE PACKAGE STANDARD
    IS
     type BOOLEAN is (FALSE, TRUE);
      type NUMBER is NUMBER_BASE;
      subtype INTEGER is NUMBER(38,);

When it comes to datatypes, PL/SQL supports the usual suspects and a whole lot more. This section provides only a quick overview of the various predefined datatypes.

Character data

PL/SQL supports both fixed- and variable-length strings as both traditional character and Unicode character data. CHAR and NCHAR are fixed-length datatypes; VARCHAR2 and NVARCHAR2 are variable-length datatypes. Here is a declaration of a variable-length string that can hold up to 2,000 characters:

    DECLARE
       l_accident_description VARCHAR2(2000);

Oracle also supports very large character strings, known as LONGs and LOBs. These datatypes allow you to store and manipulate very large amounts of data—a LOB can hold up to 128 terabytes of information in Oracle Database 10g. (Use LONGs only for compatibility with existing code. The future lies with LOBs.) The character ...