Let us learn %Type of Oracle

Introduction

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.

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

END;

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

DECLARE
 var_CustName Customer.CustomerName%TYPE := 'TestCustomer';
 var_PhoneNumber Customer.PhoneNumber%TYPE;
BEGIN
 /* Some PL/SQLcode */
	Select PhoneNumber
	Into var_PhoneNumber
	From Customer
	Where CustomerName = var_CustName;
 DBMS_OUTPUT.PUT_LINE(var_PhoneNumber);
END;
/

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

Conclusion

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