What  are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?

 %  TYPE  provides  the data type of a variable or a database column to that variable. % ROWTYPE  provides the record type that represents a entire row of a table or view or columns selected in the cursor. The advantages are : I. Need not  know about variable's data typeii.  If  the  database  definition of a column in a table changes, the data type of a variable changes accordingly.
  • Interview Candidate
  • 35
  • 47792

Showing Answers 1 - 35 of 35 Answers

StoneColdSteveAustin

  • Oct 10th, 2005
 

eg:

ename                    employees.emp_id%type;

here ename is the variable to be declared.

employees is the table name

emp_id is the column for that table.

%type indicates to assign the datatype of emp_id column in employees table for the ename variable.

  Was this answer useful?  Yes

Sebastian Thomas

  • Jan 18th, 2006
 

Advantage is, if one change the type or size of the column in the table, it will be reflected in our program unit without making any change.

satish.m.v

  • May 21st, 2007
 

%Type is defined as a difination for a perticular columm, It is a scalar type.

EX:
V_EMPNO?EMP.EMPNO%Type;
Where as

%ROW TYPE is a defination for a table or it is a composite type.

Ex:
V_EMP? EMP.%ROWTYPE.

Yestha Bapna

  • Aug 14th, 2007
 

%TYPE can be used with the column name preceded with table name to decide the datatype and length of the variable at runtime. In this case there is no dependency on changes made to the data structure.

%ROWTYPE can be used to declare the variable having the same no. of variables inside it (ROWTYPE) as no. of columns there in the table. In this case columns selected with SELECT statement must match with variables inside the rowtype variable. If not then induvidually refer these variables inside the ROWTYPE variables

sunilbidgar

  • Apr 18th, 2008
 

%Type -  With %Type attribute we can inherit the datatype of another variable or an attribute of table into currently declared variable.
eg.

declare
    var1 production.invoice.itemno%type;

%Rowtype -  With %rowtype attribute we can inherit the datatypes entire variable into a record varible from another record variable or from all attributes of table into currently declared record variable.
eg.

declare
    record1 production.invoice%rowtype;

here u can refer any attrbute as record1.itemno

Advantage is u need not to change the pl/sql code even if table attribute changes.
u need not to remember detail datatypes of table while coding pl/sql

krishnaindia2007

  • May 6th, 2008
 

%TYPE is used to declare a variable with the same type as that of a database table column.%ROWTYPE is used to declare a record as same type found in database table.These two provides data independence and allows you to adopt database changes due to new business requirements.You need not know datatype and size in advance.

ajayraj

  • Dec 15th, 2010
 

% TYPE is used to define a varibale as same data type of the column of associated table.
%ROWTYPE provides a record type that represents a row in database table.

The benefit of %TYPE and %ROWTYPE is when u are fetching the data from a table in procedure and after that data type of column or size is changed then no need to modify the procedure.

  Was this answer useful?  Yes

nirmal1in

  • Dec 25th, 2010
 

The %TYPE is used to define the varible of particular type for column exist on the table.
The %ROWTYPE is used to define variable of row type of table.

- It is good practice to use %TYPE and %ROWTYPE instead of defining varible of simple type. In case of any change in the data type of columns and no need to make any changes to code.
- %ROWTYPE can be used to fetch complete row instead of defining varible for each field to hold the data while processing.

  Was this answer useful?  Yes

Jitendra

  • Feb 28th, 2012
 

ename variable will create with the data type of emp_id column of the employees table

for eg. if emp_id is of numeric type than ename should be numeric.

  Was this answer useful?  Yes

Kishore.Dondla

  • Jun 29th, 2012
 

We Can USE These Data Types in Stored Procedures ,function and Packages as IN Parameter.

  Was this answer useful?  Yes

Yaseen

  • Aug 9th, 2012
 

if using %type and %rowtype , no need to know the the data type of the table column . and once the development is finished , the type of column is changed it will not effect the coding.

  Was this answer useful?  Yes

PRADEEP

  • Oct 16th, 2012
 

%rowtype is associated entire table(if we want to declare all columns then we need to declare %rowtype).

%type is associated with one column .(if we want to declare entire column then we need to declare %type).

  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