Oracle %TYPE and %ROWTYPE Attributes

In PL/SQL, variables can inherit datatypes from database objects like columns or tables. The %TYPE and %ROWTYPE attributes simplify variable declarations, making your code more resilient to changes in the underlying database structure. If a table's column datatype changes, variables declared with these attributes automatically adapt at runtime, reducing maintenance effort.

The %TYPE attribute declares a variable that matches the datatype of a specific database column or another variable. This is useful when you need a variable to store a single column's value without hardcoding its datatype.
Syntax and Example

v_prod_id product.product_id%TYPE;

Here, `v_prod_id` inherits the datatype of the `PRODUCT_ID` column in the `PRODUCT` table.

The %ROWTYPE attribute declares a record variable that mirrors the structure of an entire row in a table, view, or cursor result set. Each field in the record corresponds to a column, with the same name and datatype.

product_rec product%ROWTYPE;

Here, `product_rec` is a record that can store a full row from the `PRODUCT` table. You can access individual fields like this:

product_rec.product_id := 11;

This assigns the value `11` to the `product_id` field of the record.

• Matches table structure: The record automatically includes all columns of the table or view.
• Adapts to changes: If columns are added or modified in the PRODUCT table, product_rec updates at runtime.

Note: You cannot initialize a %ROWTYPE variable in its declaration.

Both attributes can be used with cursors to inherit datatypes from query results.
Example

CURSOR rad_cursor IS
  SELECT * FROM RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
rad_val_radius rad_val.Radius%TYPE;

• rad_val is a record that inherits the structure of the cursor's result set (all columns from RADIUS_VALS).
• rad_val_radius is a variable that inherits the datatype of the Radius column within rad_val.

This approach ensures your variables align with the cursor's structure, even if the underlying table changes.

Attribute	Purpose	Scope	Example Use Case
%TYPE	Inherits the datatype of a single column or variable	Single value	Store a product ID or name
%ROWTYPE	Inherits the structure of an entire row	Multiple columns (record)	Store all details of a product record

Summary
The %TYPE and %ROWTYPE attributes make PL/SQL code flexible and maintainable by linking variable datatypes to database objects. Use %TYPE for single-column values and %ROWTYPE for entire rows. Both attributes ensure your code adapts to database changes, saving time and improving robustness.