Anchored Datatype in PL/SQL

By Manish Sharma

Anchored Datatype (% Type)

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 of Anchored Datatype.

 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 of Anchored Datatype.

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.

Anchored Datatype in PL/SQL By rebellionrider manish sharma

I have also inserted two rows into this table.

Anchored Datatype in PL/SQL By rebellionrider manish sharma

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.

  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.

  v_fname  students.first_name%TYPE;
  SELECT first_name INTO v_fname FROM students WHERE stu_id =1;
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!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn
  •          View Manish Sharma's profile on LinkedIn