Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page