Anchored data types are those data type which you assign to a variable based on a database object. They are called anchored data type because unlike the variable data type it is not dependent on that of any underlying object.

Syntax

variable_name typed-attribute%type

Where variable name is user defined name given to a variable and type attribute can be anything such as previously declared PL/SQL variable or column of a table. And at the end %type is the direct reference to the underlying database object.

Examples

For the demonstration I have created a table by the name of Students which has two columns

Stu_id with data type Number and data width 2 and First_name with data type varchar2 and data width 8.

I have also inserted two rows into this table.

Example 1: How to Declare a variable with Anchored Datatype

Next I will write an anonymous block where I will declare a variable with anchored data type and then initialize that variable by fetching value from this table.

So let’s do it.

SET SERVEROUTPUT ON;
DECLARE
v_fname students.first_name%TYPE;

Here In the declaration section I have declared a variable by the name of v_fname with identical data type as the column First Name of table Students. This means that the data type of variable v_fname will be the varchar2 with data width 8. This is the data type and data width of the column first name of our table students.

So let’s add execution section to this anonymous PL/SQL block and initialize this variable v_fname by fetching data from the table students.

BEGIN
SELECT first_name INTO v_fname FROM students WHERE stu_id =1; DBMS_OUTPUT.PUT_LINE (v_fname); END;

Here in this execution block I have a Select… Into statement using which I am fetching first name of the student whose stu_id is 1 and storing it into our variable v_fname.

That’s how we declare a variable with anchored data type.

Hope you this was helpful. Kindly please share this with your friends and help me reach out to more people. Thanks and have a great day!