Difference between %ROWTYPE and %TYPE in Oracle PLSQL

In this Oracle Blog Post, We are sharing another PLSQL Interview question in Oracle PLSQL which is Difference Between %ROWTYPE and %TYPE in Oracle PLSQL.

Before going to understand %ROWTYPE and %TYPE in Oracle PLSQL, First we should understand VARIABLE in Oracle PLSQL.

UNDERSTANDING PLSQL VARIABLES

Variables are fundamental in PL/SQL. They hold data temporarily during program execution, allowing you to manipulate and store values across operations. But here's the catch—if the variable's data type doesn't match the actual column in the table, you might end up with errors or, worse, corrupt data handling.

That's where %TYPE and %ROWTYPE come into play. These attributes ensure that your variables are always consistent with the table or column they're derived from. Instead of manually specifying a data type (and risking mismatches), you let Oracle handle it for you. This makes your code not only cleaner but also future-proof—no need to rewrite your declarations if the schema changes.

What is %TYPE in PL/SQL?

%TYPE is a PL/SQL attribute used to declare a variable with the same data type as a specific table column or another variable. It's like telling Oracle, “Hey, I want this variable to act just like that column over there.”

Syntax of %TYPE in PLSQL

variable_name table_name.column_name%TYPE;

Use Case: You want to create a variable that stores a value from a specific column in a table, and you want to ensure the variable always has the same data type as that column, even if the column’s type changes later.

Real-world Scenario: You’re creating a stored procedure that needs to reference a customer’s email address. Rather than hardcoding the data type as VARCHAR2(100) (or whatever the length is), you simply say:

DECLARE 
customer_email customers.email%TYPE;

Now, if someone changes the column type in the future—say, extends the email field to 150 characters—your code will still work without modification.

Key Features of %TYPE

Let’s break down what makes %TYPE so valuable:

1. Automatic Data Type Inheritance: %TYPE ensures your variable always aligns with the current definition of the column it references. This eliminates data-type mismatches and enhances maintainability.
2. Error Reduction: No more guessing or mismatching data types between your code and the table. This helps avoid run-time errors and logic bugs.
3. Improved Maintainability: When the structure of a table changes (e.g., the column data type changes), you don’t need to update your PL/SQL code. Oracle takes care of that behind the scenes.

Example of %TYPE Usage

DECLARE
   emp_salary employees.salary%TYPE;
   emp_name employees.last_name%TYPE;
BEGIN
   SELECT salary, last_name INTO emp_salary, emp_name
   FROM employees
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_salary);
END;

Explanation:

• Line 2-3: We declare two variables, emp_salary and emp_name, that will inherit the data types of salary and last_name columns from the employees table.
• Line 5-7: We select the values from the table directly into these variables.
• Line 9-10: We print the values using DBMS_OUTPUT.

If USER ever updates the data type of salary from NUMBER(8,2) to NUMBER(10,2) or changes last_name to a longer VARCHAR2, this code still works flawlessly. No edits needed.

What is %ROWTYPE in PLSQL?

Now that we’ve tackled %TYPE, let’s explore its big brother: %ROWTYPE. If %TYPE lets you mirror a single column’s data type, %ROWTYPE lets you grab the entire row’s structure. Think of it as a shortcut to declare a record that matches a whole table row, field by field.

Syntax of %ROWTYPE in PLSQL

record_variable table_name%ROWTYPE;

Use Case: You need to fetch or process an entire row from a table and don’t want to declare a separate variable for each column manually.

Real-world Scenario: Say you want to retrieve all the data for a specific employee from the employees table. Instead of declaring variables for employee_id, first_name, last_name, salary, and every other field, you just say:

DECLARE
   emp_record employees%ROWTYPE;

Boom! You’ve now got a single variable (emp_record) that contains every column from the employees table, each as a field within that record. It's like grabbing an entire row into one container.

Key Feature's of %ROWTYPE

1. Schema Synchronization: Just like %TYPE, if the table structure changes—say, a new column is added—your %ROWTYPE variable automatically includes it. No code changes required.
2. Perfect for Cursor Operations: %ROWTYPE works beautifully with explicit and implicit cursors. It simplifies row-by-row processing in loops.
3. Readable & Maintainable: One variable that reflects the entire row makes your code easier to read, understand, and maintain over time.

Example of %ROWTYPE

DECLARE
   emp_rec employees%ROWTYPE;
BEGIN
   SELECT * INTO emp_rec
   FROM employees
   WHERE employee_id = 101;

   DBMS_OUTPUT.PUT_LINE('ID: ' || emp_rec.employee_id);
   DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.first_name || ' ' || emp_rec.last_name);
   DBMS_OUTPUT.PUT_LINE('Salary: ' || emp_rec.salary);
END;

Explanation:

1. Line 2: We declare emp_rec as a record based on the structure of the employees table.
2. Line 4: We select an entire row into the record using SELECT * INTO.
3. Line 6-8: We access individual fields using dot notation to print employee details.

This method is especially handy when dealing with tables that have lots of columns. Instead of juggling 10+ variables, one %ROWTYPE record handles everything gracefully.

Difference Between %TYPE and %ROWTYPE

Feature	%TYPE	%ROWTYPE
Scope	Single column	Entire row
Usage	Declares variable with same type as a column	Declares record matching row structure of table
Syntax	var_name table.column%TYPE	record_name table%ROWTYPE
Access Type	Direct variable	Dot notation (record.field)
Use in Cursors	Not suitable	Ideal for row-by-row cursor operations
Flexibility	Limited to one column	Adapts to table structure entirely
Readability	Good	Better for full-row operations
Maintenance	Easy if column type changes	Easier for dynamic row structure changes

Conclusion

In the world of PL/SQL, %TYPE and %ROWTYPE aren't just conveniences—they're essential tools that boost the robustness, maintainability, and clarity of your code. %TYPE offers a precise way to align variables with column definitions, while %ROWTYPE gives you a powerful mechanism to mirror full rows without the clutter of multiple declarations.

Here's the bottom line:

• Use %TYPE when you only need one or two fields and want to stay tightly aligned with the column definitions.
• Use %ROWTYPE when you want to handle or retrieve an entire row in one clean statement.
• Combine both when your PL/SQL programs become more complex.

Both attributes promote best practices in PL/SQL by eliminating hardcoding, enhancing flexibility, and making your applications more resilient to schema changes. Whether you're building enterprise HR systems, e-commerce platforms, or core banking software, leveraging %TYPE and %ROWTYPE will make your code smarter, cleaner, and more adaptable.

Watch Video of %TYPE and %ROWTYPE in Oracle PLSQL.