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


 

HTML Text

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.


 

Datatype

Description

binary_integer

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).

natural

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

positive

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.

boolean

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.


Records

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

Student_rec.first_name

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