Let us learn %Type of Oracle

Posted by in Oracle category on for Beginner level | Points: 250 | Views : 2754 red flag

In this article we will learn about %Type of Oracle


Oracle has many features and out of which %Type is one of them.This article will help us to learn about that.

Straight to Experiment

Say we have a table as under

Create Table Customer
	CustomerName VARCHAR2(50)
	,PhoneNumber INT

As can be figure out that we have a very basic table with just two columns viz. CustomerName of type VARCHAR2 and PhoneNumber of Type INT

Now, let us consider we have variables for holding the datatype of a table's column.

	var_CustName  VARCHAR(50) := 'TestCustomer';
	var_PhoneNumber INT ;
	/* Some PL/SQLcode */
	Select PhoneNumber
	Into var_PhoneNumber
	From Customer
	Where CustomerName = var_CustName;


We are storing the PhoneNumber record in the var_PhoneNumber field for a particular Customer. Now let us think that because of some business needs we need to change the "PhoneNumber" field from INT to VARCHAR.Also imagine that we have many other store procedures where we have already implemented the 'var_PhoneNumber' as INT.

Alter Table Customer
Modify ( PhoneNumber VARCHAR2(50));

Now imagine the pain of changing the datatype of 'var_PhoneNumber' from INT to VARCHAR2 in all those stored procedures where it has been used. Fortunately, ORACLE supports %TYPE. By using this we can specify the type to the variable same as specified for the column and accomodate changes made in the column data types.

The general syntax can be : Variable_Name Table_Name.Column_Name%Type

 var_CustName Customer.CustomerName%TYPE := 'TestCustomer';
 var_PhoneNumber Customer.PhoneNumber%TYPE;
 /* Some PL/SQLcode */
	Select PhoneNumber
	Into var_PhoneNumber
	From Customer
	Where CustomerName = var_CustName;

As can be figure out that, now the variables are strictly typed.


Hope this article has given a proper insight about the working of %Type in Oracle.Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)