Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

PL/SQL %TYPE And %ROWTYPE
Oracle Tips by Burleson
 

PL/SQL allows variables to be declared dynamically, depending on the type of a referenced column or variable. Consider this code:

vFirstName     STUDENTS.first_name%TYPE;
Students_rec   Students_cur%ROWTYPE;

The vFirstName variable will have the same datatype and size as the column first_name in the STUDENTS table. If the length of the first_name column is increased, the length of vFirstName will also be increased. The use of %TYPE allows you to develop a variable that is dependent on the structure of your data; if the structure of the data changes, your variable will change as well.

Using %TYPE references to variables is an excellent idea for making maintenance of your code simpler; however, this is not a complete solution. Imagine that you have a variable referencing a column of type integer ; if this changes to type number or one of the other subtypes of type number, %TYPE has done its job.

Now imagine that your variable is a %TYPE reference to a number column, perhaps a unique sequence number for records. If a business rule changes, you may suddenly find yourself with a sequence that includes characters and a block of code that calls a numeric function such as min() or max(). In this instance, your code will have to change.

Admittedly, this is a fairly radical change in the data model that your code is based upon, but it has been known to occur. Using %TYPE is a good idea (fields changing in size is quite common, especially during system development), but don’t expect %TYPE to solve all your future maintenance woes.

%ROWTYPE is very similar to %TYPE, except the it creates a variable of record type. %ROWTYPE is typically used with cursors, but can duplicate the structure of a table or record as well.

  

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter