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 %TYPE attribute is a powerful feature designed to enhance the readability, maintainability, and flexibility of code by associating variables with database columns or other variables. .

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;

Table and Column Association 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;

Record Fields example

%TYPE can also be applied to record variables, providing a concise way to define records based on the structure of database tables or other record variables.

DECLARE
   TYPE emp_rec IS RECORD (
      emp_id   employees.employee_id%TYPE,
      emp_name employees.first_name%TYPE
   );
   
   v_employee emp_rec;
BEGIN
   -- Code logic using v_employee
END;

In summary, the %TYPE attribute in PL/SQL offers several advantages, including data type consistency, adaptability to schema changes, improved code readability, reduced maintenance effort, and enhanced code portability. Its usage is a best practice in PL/SQL development for creating robust and maintainable code.

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.