%TYPE Attribute

The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, instead of hardcoding the type names. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased. For more information, see "Using the %TYPE Attribute".

Syntax

Description of the illustration type_attribute.gif

Keyword and Parameter Description

collection_name

A nested table, index-by table, or varray previously declared within the current scope.

cursor_variable_name

A PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.

db_table_name.column_name

A table and column that must be accessible when the declaration is elaborated.

object_name

An instance of an object type, previously declared within the current scope.

record_name

A user-defined or %ROWTYPE record, previously declared within the current scope.

record_name.field_name

A field in a user-defined or %ROWTYPE record, previously declared within the current scope.

variable_name

A variable, previously declared in the same scope.

Usage Notes

The %TYPE attribute is particularly useful when declaring variables, fields, and parameters that refer to database columns. Your code can keep working even when the lengths or types of the columns change.

The NOT NULL column constraint is not inherited by items declared using %TYPE.

Examples

DECLARE
-- We know that BUFFER2 and BUFFER3 will be big enough to hold
-- the answers. If we have to increase the size of BUFFER1, the
-- other variables will change size as well.
   buffer1 VARCHAR2(13) := 'abCdefGhiJklm';
   buffer2 buffer1%TYPE := UPPER(buffer1);
   buffer3 buffer1%TYPE := LOWER(buffer1);

-- We know that this variable will be able to hold the contents
-- of this table column. If the table is altered to make the
-- column longer or shorter, this variable will change size as well.
   tname user_tables.table_name%TYPE;

-- %TYPE is great for subprogram parameters too, no need to
-- recompile the subprogram if the table column changes.
   PROCEDURE print_table_name(the_name user_tables.table_name%TYPE)
   IS
   BEGIN
      dbms_output.put_line('Table = ' || the_name);
   END;
BEGIN
   SELECT table_name INTO tname FROM user_tables WHERE ROWNUM < 2;
   print_table_name(tname);
END;
/

Constants and Variables, %ROWTYPE Attribute