%TYPE attribute - Oracle

In the above program, we declared a variable "v_ename" as being of type "varchar(10)." What if we don't know the data type (or data length) of the field "ename"? In general, we will switch back to the SQL prompt and issue a DESCRIBE statement to show all of the columns and datatypes (and of course the lengths, too) of a particular table. What if DBA increases the width of "ename" column without informing you (which happens very often)? Your program may not work correctly.

So, we need to have a special mechanism with which a variable should find the data type and width dynamically and automatically at runtime. This is where %TYPE comes in. Let us take a closer look at it, with a simple example.

Declare
     v_ename emp.ename%Type;
     v_empno emp.empno%type := &empno;
begin
     select ename into v_ename
     from emp
     where empno=v_empno;
     dbms_output.put_line('Name: '||v_ename);
end;

This is very similar to the first example, except for the declaration section. "v_ename" is declared as being of type "emp.ename%type." What does it mean? It means that the data type of "v_ename" would be the same as the data type of the "ename" column in the "emp" table. Similarly, the other variable, "v_empno," is also declared.

We can declare multiple variables and fetch accordingly, as shown in the following example:

Declare
     v_empno emp.empno%type := &empno;
     v_ename emp.ename%type;
     v_sal emp.sal%type;
     v_job emp.job%type;
begin
     select ename,sal,job into v_ename,v_sal,v_job
     from emp
     Where empno=v_empno;
     dbms_output.put_line('Name: '|| v_ename);
     dbms_output.put_line('Salary: '|| v_sal);
     dbms_output.put_line('Job: '|| v_job);
end;