PL/SQL %TYPE Attribute

Oracle PL/SQL provides a special data type called the %TYPE data type. The %TYPE data type allows you to declare a variable that is associated with a column in a database table.

The Oracle PL/SQL %TYPE attribute allow you to declare a constant, variable, or parameter to be of the same data type as previously declared variable, record, nested table, or database column.

To use the PL/SQL %TYPE data type, you first need to declare a variable. You can then use the variable in your PL/SQL code just like any other variable.

The %TYPE attribute can be used with variables, records, nested tables, and database columns.

Syntax:

identifier Table.column_name%TYPE;

Example:

Here is an example of how to declare a variable using the PL/SQL %TYPE attribute:

declare
v_name employee.lastname%TYPE;
v_dep  number;
v_min_dep v_dep%TYPE:=31;
begin
select lastname 
into v_name 
from EMPLOYEE 
where DEPARTMENTID=v_min_dep;
DBMS_OUTPUT.PUT_LINE('v_name: '||v_name);
end;

Oracle PL/SQL %TYPE Attribute is very useful when you want to write code that is independent of the database column’s data type. It is also helpful in reducing coding errors.