PL/SQL DATA TYPES

Both PL/SQL and Oracle have their foundation in SQL. Most SQL data types are native to Oracle’s data dictionary. Hence, there is a very easy integration of PL/SQL code with the Oracle Engine.

The default data types that can be declared in PL/SQL are number (for storing numeric data), char (for storing character data), date (for storing date and time data), boolean (for storing TRUE, FALSE or NULL), number, char and date data types can have NULL values.

The %TYPE attribute provides for further integration. PL/SQL can use the %TYPE attribute to declare variables based on definitions of columns in a table. Hence, if a column’s attributes change, the variable’s attributes will change as well. This provides for data independence, reduces maintenance costs, and allows programs to adapt to changes made to the table.

%TYPE declares a variable or constant to have the same data type as that of a previously defined variable or of a column in a table or in a view. When referencing a table, a user may name the table and the column, or name the owner, the table and the column.

NOT NULL causes creation of a variable or a constant that cannot be assigned a null value. If an attempt is made to assign the value NULL to a variable or a constant that has been assigned a NOT NULL constraint, Oracle senses the exception condition automatically and an internal error is returned.

As soon as a variable or constant has been declared as NOT NULL, it must be assigned a value. Hence every variable or constant declared as NOT NULL needs to be followed by a PL/SQL expression that loads a value into the variable or constant.

VARIABLES

Variables in PL/SQL blocks are named variables. A variable name must begin with a character and can be followed by a maximum of 29 other characters.

Reserved words cannot be used as variable names unless enclosed within double quotes. Variables must be separated from each other by at least one space or by a punctuation mark.

Case is insignificant when declaring variable names. A space cannot be used in a variable name. A variable of any data type either native to the Oracle Engine such as number, char, date, and so on or native to PL/SQL such as Boolean (i.e. logical variable content) can be declared.

Assigning Values to Variables

The assigning of a value to a variable can be done in two ways:

Ø  Using the assignment operator := (i.e. a colon followed by an equal to sign).

Ø  Selecting or fetching table data values into variables.

CONSTANTS

Declaring a constant is similar to declaring a variable except that the keyword constant must be added to the variable name and a value assigned immediately. Thereafter, no further assignments to the constant are possible, while the constant is within the scope of the PL/SQL block.

Raw

Raw types are used to store binary data. Character variables are automatically converted between character sets by Oracle, if necessary. These are similar to char variables, except that they are not converted between character sets. It is used to store fixed length binary data. The maximum length of a raw variable is 32,767 bytes. However, the maximum length of a database raw column is 255 bytes.

Long raw is similar to long data, except that PL/SQL will not convert between character sets. The maximum length of a long raw variable is 32,760 bytes. The maximum length of a long raw column is 2 GB.

Rowid

This data type is the same as the database ROWID pseudo-column type. It can hold a rowid, which can be considered as a unique key for every row in the database. Rowids are stored internally as a fixed length binary quantity, whose actual fixed length varies depending on the operating system.

ROWID is a pseudo-column that has a unique value associated with each record of the database.

LOB Types

The LOB types are used to store large objects. A large object can be either a binary or a character value upto 4 GB in size. Large objects can contain unstructured data, which is accessed more efficiently than long or long raw data, with fewer restrictions. LOB types are manipulated using the DBMS_LOB package. There are four types of LOBs:

BLOB (Binary LOB) - This stores unstructured binary data upto 4 GB in length. A blob could contain video or picture information.

CLOB (Character LOB) - This stores single byte characters upto 4 GB in length. This might be used to store documents.

BFILE (Binary File) - This stores a pointer to read only binary data stored as an external file outside the database.

Of these LOBs, BFILE is an external to the database. Internal objects store a locator in the Large Object fcolumn of a table. Locator is a pointer that specifies the actual location of LOB stored outside the database. The LOB locator for BFILE is a pointer to the location of the binary file stored by the operating system. The DBMS_LOB package is used to manipulate LOBs. Oracle supports data integrity and concurrency for all the LOBs except BFILE as the data is stored outside the database.

Storage for LOB data

The area required to store the LOB data can be specified at the time of creation of the table that includes the LOB column. The create table command has a storage clause that specifies the storage characteristics for the table.

Syntax:

CREATE TABLE ( ,

, CLOB, ...);

Logical Comparisons

PL/SQL supports the comparison between variables and constants in SQL and PL/SQL statements. These comparisons, often called Boolean expressions, generally consist of simple expressions separated by relational operators (</>, =, < >, >=, <=) that can be connected by logical operators (AND, OR, NOT). A Boolean expression will always evaluate to TRUE, FALSE or NULL.