1. Introduction

The %TYPE attribute lets you declare a data item of the same data type as a previously declared variable or column. It is not necessary to know the type of the variable while using %TYPE attribute.
For example, if you want to declare a variable in a PL/SQL block, which is of the same type of a column of a table, %TYPE can be used.

Following example will declare a variable name which is of the same type as name column of employees table.

name employees.name%TYPE;

2. Syntax

The syntax for using %TYPE is:

referencing_item referenced_item%TYPE;

The referencing item inherits the following from the referenced type:

• Data type and size
• Constraints (unless the referenced item is a column)

The referencing item does not inherit the initial value of the referenced item. So if the referencing inherits a NOT NULL constraint, you should provide an initial value to it.

%TYPE is commonly used to refer to the type of a table column. Following is the syntax:

variable_name table_name.column_name%TYPE;

3. Advantage of %TYPE

The advantage of using %TYPE is that if the declaration of the referenced item changes, then the type of the referencing item changes accordingly.

4. Examples

4.1 Declaring variable of same type as column

In this example, we have declared a variable which has same type as of name column of employee table.

DECLARE
    name employee.name%TYPE := 'James';
BEGIN
    DBMS_OUTPUT.PUT_LINE('name: ' || name);
END;

Output

name: James

4.2 Declaring variable of same type as another variable

You can define a variable of the same type of another variable. In this example, we have declared a variable another_name which has the same type as name.

DECLARE
    name employee.name%TYPE := 'James';
    another_name name%TYPE := 'John';
BEGIN
    DBMS_OUTPUT.PUT_LINE('name: ' || name);
    DBMS_OUTPUT.PUT_LINE('another_name: ' || another_name);
END;

Output

name: James
another_name: John