In Oracle PL/SQL, what is the difference between the %TYPE and %ROWTYPE attributes when declaring variables?

Introduction / Context:
In Oracle PL/SQL, developers often want variable declarations that automatically match the data types of columns in database tables or the structure of query results. The attributes %TYPE and %ROWTYPE are provided for this purpose. Understanding the difference between these two attributes is a common interview question because it tests whether a candidate can write maintainable, schema anchored PL/SQL code that adapts to table changes without manual rewrites.

Given Data / Assumptions:

• The discussion is about Oracle PL/SQL variable and record declarations.
• There are existing table columns or previously declared variables whose data types we want to reuse.
• Sometimes we need a single variable that matches one column, and sometimes we need an entire record that matches a whole row.
• We want to reduce hard coded data types so that changes in table definitions cause minimal code changes.

Concept / Approach:
The %TYPE attribute is used to declare a scalar variable that has exactly the same data type as a specific table column or as another previously declared variable. This is ideal when we want to store a single column value and keep the declaration in sync with the table definition. The %ROWTYPE attribute, in contrast, declares a composite record variable whose fields correspond to all the columns of a table, view, or cursor. This is useful when we want to hold an entire row at once, for example when fetching from a cursor or performing record level operations. Both attributes provide schema level anchoring, but at different granularities: single column versus whole row.

Step-by-Step Solution:
Step 1: Consider a table EMP with columns EMPNO, ENAME, SAL and DEPTNO. Step 2: To declare a variable that always has the same type as EMP.SAL, we use: v_sal EMP.SAL%TYPE; Step 3: To declare a record that can hold a full row of EMP, we use: v_emp EMP%ROWTYPE; Step 4: Notice that v_sal is a single scalar variable, while v_emp is a record with fields such as v_emp.empno, v_emp.ename and so on. Step 5: Recognise that %TYPE anchors one variable to one column or variable type, whereas %ROWTYPE anchors a record to the full row structure of a table, view or cursor.

Verification / Alternative check:
If the column EMP.SAL is later altered from NUMBER(7,2) to NUMBER(9,2), any variable declared as EMP.SAL%TYPE automatically reflects the new type without changing the PL/SQL code. Similarly, if a new column is added to EMP, the record variable EMP%ROWTYPE automatically gains a corresponding field. This behaviour confirms that %TYPE is tied to a single column type and %ROWTYPE is tied to the whole row structure. If %TYPE and %ROWTYPE were identical, we would not see this difference in behaviour between scalar variables and composite records.

Why Other Options Are Wrong:
Option B reverses the meaning, incorrectly claiming that %TYPE is for full rows and %ROWTYPE is for single columns. Option C claims that %TYPE is only for cursors and %ROWTYPE is only for table columns, which is not correct because both can anchor to tables, views or cursor definitions. Option D wrongly links %TYPE and %ROWTYPE to numeric or character data types, which is unrelated to their real purpose. Option E says there is no difference, which contradicts the clear distinction between scalar column based variables and row based records in PL/SQL.

Common Pitfalls:
A common mistake is declaring many scalar variables with hard coded types such as NUMBER or VARCHAR2 rather than anchoring them with %TYPE, which increases maintenance effort when the table changes. Another pitfall is trying to fetch a full row into a scalar variable instead of using a %ROWTYPE record. Developers should remember that %TYPE is for matching a single column or variable type, while %ROWTYPE is for matching an entire row structure. Using these attributes properly makes PL/SQL code more robust and easier to maintain.

Final Answer:
%TYPE declares a variable with the same data type as a single table column or another variable, whereas %ROWTYPE declares a record variable with the same structure as an entire row in a table or cursor.