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.
