What is difference between % ROWTYPE and TYPE RECORD ?

 %  ROWTYPE  is to be used whenever query returns a entire row of a table or view. TYPE  rec  RECORD is to be used whenever query returns columns of differenttable or views and variables.      E.g.  TYPE  r_emp is RECORD (eno emp.empno% type,ename emp ename %type);     e_rec emp% ROWTYPE     cursor c1 is select empno,deptno from emp;     e_rec c1 %ROWTYPE.

Showing Answers 1 - 13 of 13 Answers


  • Sep 20th, 2005

basically the %rowtype is used in case of fetching the values of the cursor irrespective of how many columns in it and also the data types associated with the tables column.for eg.


cursor c_example is select * from emp;

v_emp emp%rowtype;


for v_emp in c_example loop



end loop;


the type record is a object oriented concept related to pl/sql tables, tables with datastructure format etc.

create type ty_name is record ( a number,

                                            b varchar2(10));

this type stores data in a record fashion with 2 columns.


  • Apr 18th, 2008

%rowtype is an attribute to inherit datatypes of attributes of a table into a RECORD variable. Type record is a keyword to create record type using either explicitly specifying atrributes or by implecitly inheriting attributes from a table or a existing cursor.

  Was this answer useful?  Yes


  • May 5th, 2008

TYPE RECORD is a composite datatype. It consits of multiple pieces of information, called fields. TYPE RECORD fields can be defined by the user. Eg:

   TYPE extra_book_info_t
      IS RECORD (
      title books.title%TYPE,
      is_bestseller BOOLEAN

   first_book extra_book_info_t;

here, 'title' is the data type defined in books table. We can declare a RECORD based on this type.

Where as %ROWTYPE is a direct link to the data type of the table.columns, EG:
   bestseller books%ROWTYPE;

The advantage of TYPE RECORDS is you can pass these to Functions or Procedures which can reduce the parameter size or a repitative work.

Hope this give a better explanation.

%ROWTYPE is used when you need to work with complete record.
TYPE RECORD is used to create your own data type with specificed number of values to hold.
Suppose If a table has 20 columns and you need to work with only seven columns . If I use %ROWTYPE, I get all 20 values unnecessarily. At the same time, my program will be bit clumsy if I use seven %TYPE declarations. A better way to solve this solution is by defining my own data type, which can hold seven values.


  • Nov 1st, 2011

there is no need to declare v_emp as we are using for loop....

  Was this answer useful?  Yes


  • Jan 23rd, 2013

%rowtype:- %rowtype means it is will fetch entire row values.

Type record: Type record means we can fetch record from more than one table.

  Was this answer useful?  Yes


  • Apr 15th, 2013

row type is used to hold the total row values

  Was this answer useful?  Yes


  • Sep 19th, 2013

%ROWTYPE can be used to declare a record of all columns in a table. They are the same.

  Was this answer useful?  Yes

%rowtype holds all the rows of a table while you are fetching the rows using a cursor where as the type_rec object can hold the specific columns based on whatever the columns are defined in the type_record variable.

  Was this answer useful?  Yes


  • Mar 25th, 2015

can we use %TYPE to access data from multiple tables like emp,dept etc.

  Was this answer useful?  Yes


  • Apr 24th, 2015

When we declare a var as %row type, then the variable holds all the column type attributes. Developer does not need to know what the individual column types are. Type record is used when we declare a variable to be of the records of the table. 

  Was this answer useful?  Yes


  • Apr 30th, 2015

This is my understanding: Declare : eno employee.emp_id%type - specifically tells you that the variable type or data that it will be holding will be same as th e employee_id type in the employee table .If employee_id in the employee table is declared as of type number(4) then eno will also be the same type.So you are essentially telling which tables column type you want the variable to be.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions