%TYPE in Oracle PL/SQL with Example

In this Post, We are going to explore %TYPE in Oracle PL/SQL.

What is %TYPE in Oracle PLSQL?

• %Type is a special  data type in PLSQL which is used for declaring variables, fields, and parameters with the same data type as an existing database column or another variable. This mechanism enhances code maintainability and reduces the risk of data type-related errors.
• %Type is also called dynamic datatype.

Syntax of %TYPE in Oracle PLSQL

v_identifier table.column_name%type;

Where:

• v_identifier: The name you want to give to your variable.
• table.column_name: The name of the column or variable whose data type you want to inherit from table.

Example of %TYPE in Oracle PLSQL

Let's say you have a table named EMPLOYEES with a column EMPLOYEE_ID of type NUMBER.

DECLARE
v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE; 
BEGIN
-- ... your PL/SQL code ...
END;
/

In this example, the variable v_employee_id will automatically have the same data type as the EMPLOYEE_ID column in the EMPLOYEES table.

Real-World Use Case:

Imagine you're writing a procedure to update an employee's salary. Using %TYPE ensures that the variable holding the new salary has the exact same data type as the SALARY column in the EMPLOYEES table, preventing potential data type mismatch errors.

CREATE OR REPLACE PROCEDURE update_employee_salary (
  p_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
  p_new_salary IN EMPLOYEES.SALARY%TYPE
)
AS
BEGIN
  UPDATE EMPLOYEES
  SET SALARY = p_new_salary
  WHERE EMPLOYEE_ID = p_employee_id;
END;

/

Advantages of %TYPE in Oracle PLSQL

The primary benefit of using %TYPE lies in maintainability. If the data type of the referenced column ever changes (e.g., from NUMBER to DECIMAL), all variables declared with %TYPE based on that column will automatically inherit the new data type. This eliminates the need to manually update multiple variable declarations, significantly reducing the risk of errors and making your code more robust and adaptable to schema changes.

That's all for today, I hope this (%TYPE in Oracle PL/SQL with Example) post will be helpful for you. If you like the post then share your view in the comment box.