Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




PL/SQL Datatypes
Oracle Tips by Burleson

Table 2.3 lists a number of datatypes that are usable inside PL/SQL blocks, in addition to the default datatypes supported by the Oracle7 database.

Table 2.3 PL/SQL supported datatypes.





Variables of this type store signed integers, ranging from -231 - 1 through 231 -1 (-2147483647..2147483647). Unlike number values, variables of the binary_integer type (and its subtypes) can be used in expressions without being converted (theoretically providing a performance boost).


A subtype of the binary_integer type that holds only positive integers ranging from 0 through 231 - 1 (0..2147483647).


A subtype of the binary_integer type that holds only positive integers ranging from 1 through 231 - 1 (1..2147483647).

dec, decimal, double precision, float, integer, int, numeric, real, smallint

Subtypes of the number type that have the same constraints and range of values as the number type.


This type contains the values TRUE and FALSE. Variables of this type can also contain a NULL value. Since this datatype isn’t implicitly supported by the database engine, you cannot define a column using this type or select output into a variable of this type.


In addition to the datatypes listed in Table 2.3, PL/SQL allows developers to define custom datatypes.

TIP:  Datatype Conversions

Like Oracle and SQL, PL/SQL supports both implicit and explicit conversions of datatypes. The use of explicit conversions is strongly suggested.


PL/SQL allows developers to create a record containing one or more columns. To use records, the record type must be specified before a variable can be created based on the type; this is shown by Listing 2.14. Figure 2.8 shows the structure of a record.

Listing 2.14 A PL/SQL record declaration.

     TYPE Student_rectype IS RECORD
         (first_name          varchar2 (12),
          last_name           varchar2 (15),
          gpa                 number (3, 2));
     Student_rec     Student_rectype;

Individual columns within Student_rec are referenced as


Records are composite datatypes, constructed by the developer to suit a particular set of needs. ( Scalar datatypes are those datatypes that are automatically supported by the database engine and PL/SQL, like varchar2, integer, and date .) The values returned from cursors are often fetched into record variables, which are then referenced by PL/SQL statements both as whole records and as individual columns.


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter