Oracle %TYPE attribute.

Preamble

In Oracle PL/SQL, the %TYPE attribute for a variable provides the database column data type. This is especially useful when declaring variables that will contain the values of the database table columns.

Syntax for declaring a variable with %TYPE attribute in Oracle PL/SQL

v_name table_name.column_name%TYPE

Parameters and arguments of the attribute

• v_name – name of the variable which is assigned a value.
• table_name – the name of the database table.
• column_name – The name of the column in the table_name table.

Note:

• To declare a variable, you do not need to know the actual data type and attributes such as accuracy, size or length.
• If the accuracy in a column changes, the variable data type changes accordingly at runtime.

Example %TYPE; providing the data type to a variable

Let’s consider an example of defining a variable data type using the %TYPE attribute.

DECLARE
name VARCHAR(25) NOT NULL := 'Smith';
surname name%TYPE := 'Jones';
BEGIN
DBMS_OUTPUT.PUT_LINE('name=' || name);
DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;

In this example, the surname variable inherits the data type, size and NOT NULL limit of the variable name. Since surname does not inherit the initial value of name, the definition of surname requires an initial value (which cannot exceed 25 characters).

Example %TYPE provision of a variable with the table column data type

You can refer to the table and column, or you can refer to the owner, table and column as in the following example.

DECLARE
-- If the length of a column ever changes, this code
-- will automatically use the new length.
the_trigger user_triggers.trigger_name%TYPE;
BEGIN
NULL;
END;

In this example, the_trigger variable was assigned a trigger_name column value for the user_triggers table.

Oracle pl sql tutorial; %TYPE and %ROWTYPE attributes

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.