In a PL/SQL block, if you are querying a database table, you need to declare data variables to hold column values returned from the query. The variables can be declared of the same explicit datatype as the column in the table.

Let’s say we create a table:

 create table dept ( deptno number(2), deptname varchar2(20));

Now we have a procedure Proc1, which queries the “dept” table to get the department name for a given deptno. The code is as follows:

 create or replace procedure proc1 as 	v_deptname	varchar2(20);begin	select deptname into v_deptname	from   dept where deptno = 10;exception	when no_data_found then         raise_application_error(-20001,'Dept 10 does not exist');end;

This procedure will work fine until the table definition gets altered, such that the column “deptname” reaches varchar2(30) . If the department name for deptno =10 is longer than 20 characters in the table, then the procedure will raise an unhandled exception of “Value Error” because the variable v_deptname cannot hold more than 20 characters. If you have lots of procedures with v_deptname declared as varchar2(20), you will need to modify all the procedures.

To get around this painful process, you should declare the variables as the column type rather than the explicit datatype of the column. See the declaration below:

 v_deptname	 dept.deptname%TYPE;

Now if the column size is altered, you are not required to change any of the procedures—only recompile them. The %TYPE will automatically assign the datatype of the column to v_deptname.