Oracle %TYPE and %ROWTYPE Attributes

In PL/SQL, hardcoding datatypes creates brittle code. If a table column grows from VARCHAR2(30) to VARCHAR2(60), any PL/SQL variable declared with the old size can fail at runtime with conversion or value errors. The %TYPE and %ROWTYPE attributes solve this by anchoring your PL/SQL declarations to database objects.

Use %TYPE when you need a single value whose datatype should follow a column (or another variable). Use %ROWTYPE when you want a record that mirrors an entire row returned from a table, view, or cursor.

The %TYPE attribute declares a variable that inherits the datatype of a column, a previously declared variable, or a record field. This avoids “datatype drift” as schemas evolve.

Syntax and Example

v_prod_id   product.product_id%TYPE;
v_prod_name product.product_name%TYPE;

Here, v_prod_id and v_prod_name inherit their datatypes directly from the product table columns.

The %ROWTYPE attribute declares a record variable whose fields match the columns of a table or view (or the select-list of a cursor). Each field inherits the name and datatype of its corresponding column.

product_rec product%ROWTYPE;

You can access individual fields using dot notation:

product_rec.product_id := 11;
product_rec.product_name := 'Widget';

• Row-shaped record: Ideal for “fetch a row, process a row” logic.
• Automatic column tracking: If columns are added, removed, or datatype-modified, the record structure updates.
• Readable code: Field names stay attached to values, which reduces parameter confusion.

Important note
You cannot initialize a %ROWTYPE record directly in its declaration. Populate it by assignment or with SELECT ... INTO.

A common pattern is to fetch a complete row into a %ROWTYPE record:

DECLARE
  l_prod product%ROWTYPE;
BEGIN
  SELECT *
  INTO   l_prod
  FROM   product
  WHERE  product_id = 2;

  -- Use fields
  DBMS_OUTPUT.put_line(l_prod.product_name);
END;

Practical caution

• Invisible columns: Invisible columns do not appear in SELECT * results and are not included in %ROWTYPE record definitions for a table. If you rely on %ROWTYPE, design your queries accordingly.
• Virtual columns: Table rows containing virtual columns can complicate “record-as-row” inserts; when inserting you typically exclude virtual columns and insert individual fields instead.

Cursor anchoring is powerful when your record should match a query result (not necessarily an entire table row).

Example

DECLARE
  CURSOR rad_cursor IS
    SELECT radius_id, radius
    FROM   radius_vals;

  rad_val        rad_cursor%ROWTYPE;
  rad_val_radius rad_val.radius%TYPE;
BEGIN
  OPEN rad_cursor;
  FETCH rad_cursor INTO rad_val;
  CLOSE rad_cursor;

  rad_val_radius := rad_val.radius;
END;

• rad_val inherits the cursor select-list structure (radius_id, radius).
• rad_val_radius inherits the datatype of the radius field inside that record.

In Oracle 23ai-era schemas, %TYPE becomes even more valuable because modern column datatypes can be more expressive than older training-era workarounds:

• BOOLEAN columns: Oracle supports true boolean columns, so %TYPE can anchor PL/SQL variables directly to a boolean column instead of legacy CHAR(1) conventions.
• JSON datatype: Native JSON support continues to expand; anchoring variables with %TYPE helps keep PL/SQL aligned with your chosen JSON storage and access patterns.
• Schema evolution: Column changes happen in real systems; anchoring reduces maintenance cost and lowers risk of runtime conversion errors.

Attribute	Purpose	Scope	Example Use Case
%TYPE	Inherit datatype from a column, variable, or record field	Single value	Parameter or local variable matching product.product_id
%ROWTYPE	Inherit record structure from a table/view row or cursor select-list	Multiple fields (record)	Fetch query results into a structured record for processing

Summary
Use %TYPE for single values that must track column datatypes. Use %ROWTYPE when you want a record that matches a row or a cursor result. Together, these attributes keep PL/SQL correct under schema change, improve readability, and reduce runtime datatype errors.