Ad Code




Composite Data Types in PL/SQL

A composite type is one that has components within it A variable of a composite type contains one or more scalar variables. The main composite types available in PL/SQL are:

1) RECORD: A record represents a row in a table or a row fetched by a cursor. Records have uniquely named fields and can belong to different data types.

To declare a PL/SQL record, one first declares a table record type and then declare variables of that type.

For example,


TYPE emp_rec_type IS RECORD

(emp_no NUMBER(5) NOT NULL,

name VARCHAR2(50), dob DATE);

Once declared, one can declare records of that type:

emp_rec emp_rec_type

For example, following shows the declaration and usage of PL/SQL records:


TYPE emp_rec_type IS RECORD

(emp_no NUMBER(5) NOT NULL,

name VARCHAR2(50), dob DATE);

emp_rec emp_rec_type;


SELECT empno, name, d_dob

INTO emp_rec

FROM employee;


The following rules apply when using PL/SQL records:

i) Records of different types cannot be assigned to each other.

ii) One cannot assign a list of values to a record by using the assignment statement.

iii) Records cannot be tested for equality or inequality.

2) TABLE: PL/SQL tables are objects of type TABLE, which look similar to database tables but are not quite the same. These tables give you an array-like access to rows of data. PL/SQL tables are declare in the declaration portion of the block. They contain one column and the primary key, neither of which can be named. The primary key must be defined with the BINARY_INTEGER data type.

To declare PL/SQL table, one first declare a table type and then declare van For that type.

For example,


TYPE emp_table_type IS TABLE OF VARCHAR2(30)


Once the table is declared, declare variables of that type. For example, the following declares variables using the table type defined earlier:

emp_table emp_table_type

To reference a PL/SQL table, specify a primary key value using the array like syntax.

For example, to reference the 18th row in the PL/SQL table created in the previous example:


Assign values directly to a row in the PL/SQL table using:


TYPE emp_table_type IS TABLE OF VARCHAR2(30)




FOR new_emp IN (SELECT erne name FROM employee WHERE dept = `ADMN');


k : = k +1;

emp_table(k) = new_emp.emp_name — insert row into PL/SQL table



The following rules apply when using PL/SQL tables:

i) A loop must be used to insert values from a PL/SQL table into a  database column.

ii) A loop must be used to fetch data from a database column in a PL/SQL code.

iii) One cannot use the DELETE command to delete the contents of a PL/SQL table One must assign an empty table to the PL/SQL table being deleted.

3) VARRAY: A VARRAY is a variable length array and is a data type, which is very similar to an array in. C or Pascal. Syntactically, a VARRAY is accessed in the same way as a nested or index-by table. However, a VARRAY is implemented differently. Rather than being a sparse data structure with no upper bound, elements are inserted into a VARRAY starting at index 1 , up to the maximum length declared in the VARRAY type. Storage for a VARRAY is the same as a C or Pascal array, as opposed to the storage for a nested table, which is more like a database table.


TYPE type_name IS {VARRAY I VARYING ARRAY) (maximum_size) OF element_type [NOT NULL];

where, ‘type_name' is the name of the new VARRAY type, ‘maximum_size' is the integer specifying the maximum number of the elements in the VARRAY, and `element_type' is a PL/SQL scalar, record or object type. The 'element_type' can be specified using %type as well, but cannot be BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE or another VARRAY type.

4) REFERENCE TYPE: Once a variable of a scalar or composite type is declared in PL/SQL, the memory storage for this variable is allocated. The variable names this storage and is used to refer to it later in the program. However, there is no way to deallocate the storage and still have the variable available the — memory is not freed until the variable is no longer in scope. A reference type does not have this restriction. A reference type in PL/SQL is same as a pointer in C. A variable that is declared of a reference type can point to different storage locations over the life of the program. The reference type variables available in PL/SQL are REF CURSOR and REF object type. 

5) LOB TYPES: The LOB types are used to store large objects. A large object can be either a binary or character value upto 4 GB in size. Large objects can contain unstructured data, which is accessed more efficiently than LONG and LONG RAW data, with fewer instructions.