Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







PL/SQL best practices

Oracle Database Tips by Burleson Consulting


June 9, 2015

  PL/SQL Best Practices

  By Steve Callan 

It can be hard, as an Oracle Database administrator, to expand your PL/SQL skills due to a lack of good examples, not to mention that lines upon lines of syntax definitions can be somewhat of a turn-off.  Steve Callan shows how using PL/SQL records can simplify what you have to code - and they're easy to use too. 

I would venture to say that most DBAs (and developers) who use PL/SQL are pretty comfortable with understanding and using the declare, begin and exception sections of a block, anonymous or otherwise.  Sometimes it's hard to expand your PL/SQL skills because of a lack of good examples, and as a DBA, your programming opportunities may be limited.  Additionally, seeing lines upon lines of syntax definitions (although important in the long run) can be somewhat of a turn-off, and incomplete examples or code snippets often times leave you hanging (as in many calculus books, "proof of the following theorem is left to the curious reader."). 

If you peruse enough books, pieces of documentation and web sites, chances are you're going to come across an example that finally sinks in and makes you wonder why you weren't doing that (use of the feature) before.  So with that in mind, let's take a look at moving beyond the mundane SELECT X, Y, Z into v_X, v_Y, v_Z from SOME_TABLE construct (where you also have to declare the v_ variables too).

To move away from the mundane, we need to look at using a composite data type.  Our framework is the employees table in the HR sample schema provided by Oracle.  Let's say you need to work with the employee_id, first_name, last_name, hire_date and salary fields.  We want to declare local or user-defined variables we can select data into later on.

create procedure update_emp (empid in number) is 
v_empid number;
v_fname varchar2(20);
v_lname varchar2(30);
v_hire date;
v_sal number;
--start processing records

There are a number of things wrong or potentially wrong with the code.  Aside from no exception handling, my user-defined variables are kind of hit and miss.  It's fairly obvious the name fields are of the VARCHAR2 datatype, but how big? The last name field is actually VARCHAR2(25), not 30, and the number fields make no mention of the precision as found in the EMPLOYEES table.

With respect to sophistication, one step above this example would be to use the %TYPE attribute (anchor the variable to the field's datatype in the table).  The variable declaration would then look like this:

create procedure update_emp (empid in number) is 
v_empid employees.employee_id%type;
v_fname employees.first_name%type;
v_lname employees.last_name%type;
v_hire employees.hire_date%type;
v_sal employees.salary%type;
--start processing records 

If this code were to be part of a named block (something within a function, procedure or package), you would dodge most bullets related to changing the datatype of a field.  In other words, you wouldn't have to hunt down repeated occurrences of VARCHAR2(25) for the last name if you had coded VARCHAR2(25) in multiple locations.

In the "start processing records" area, we are now faced with the tedious coding of populating local variables (only the BEGIN section is shown).

select employee_id, first_name,
last_name, hire_date, salary
v_empid, v_fname, v_lname, v_hire, v_sal
from employees 
where employee_id = empid;
--do whatever

There is an easier, faster and more efficient way of declaring and using the variables.  We can use a PL/SQL record.  A PL/SQL record stores values of different datatypes, but only one occurrence at a time - which is the same thing we're doing by selecting values into local variables one record at a time.

There are two steps in setting up a PL/SQL record (and these steps are the same when using collections).  One way to look at the steps is to think of them as declare (or define) and instantiate.

create procedure update_emp (empid in number) is
TYPE emp_record_type IS RECORD
(v_empid employees.employee_id%type;
v_fname employees.first_name%type;
v_lname employees.last_name%type;
v_hire employees.hire_date%type;
v_sal employees.salary%type;
emp_record emp_record_type;

Now the question is about how to select values into the PL/SQL record.  It's easy.  All you have to do is select the matching columns into the emp_record.

select employee_id, first_name, last_name,
hire_date, salary
into emp_record
from employees 
where employee_id = empid;
--do whatever

If you need to reference a field, it is done by using "dot" notation.  If I wanted to set salary to 110% of the current value (a 10% raise), then the syntax would be:

emp_record.salary := emp_record.salary * 1.1;

The notation is "record_name.field_name" to describe it more formally.

With PL/SQL records, you have the option of defining your own fields (as in selecting a subset of the columns in a table, which is why a TYPE is declared first) or to make things even easier, grab all fields/columns in one fell swoop.  It's not uncommon to select all columns from a table, and a PL/SQL record makes this extremely easy to do so.  In the example below, the "pl" procedure takes an input string and calls DBMS_OUTPUT.PUT_LINE(the input string).

create procedure update_emp (empid in number) is
emp_record employees%rowtype;
select *
into emp_record
from employees 
where employee_id = empid;
pl('last name is '||emp_record.last_name);

Let's suppose you wanted to take the current values and insert them into an archive table (whose structure matches the base table).  All you need to do is this:

insert into other_table values emp_record;

After some values have been updated/modified, and wanting to update the source table with the new values, one approach would be to use a simple update statement and set the column values to whatever based on a where clause. 

But with PL/SQL records, why repeat all those "set column_A = whatever, column_B=whatever" and so on? You can update a row using the SET ROW feature within PL/SQL records.

create or replace procedure update_emp (empid in number) is
emp_record employees%ROWTYPE;
--get the data
select * into emp_record 
from employees
where employee_id = empid;
--do some assignments
emp_record.hire_date := sysdate;
emp_record.salary := emp_record.salary*1.1;
--go back and update the table
UPDATE employees 
SET ROW = emp_record 
WHERE empno=empid; 

That's pretty hard to beat in terms of having to write less code to do some fairly routine tasks.

There are other things you can do with PL/SQL records.  When declaring a TYPE, you can define as many fields as you want, and further, assign default values and NOT NULL constraints.  Fields with NOT NULL must be initialized, and a field cannot be a REF CURSOR.  If you want, you can also nest records, so that a field in one PL/SQL record is another PL/SQL record in of itself.

A distinction between PL/SQL records and collections is that a record contains related data consisting of different data types (like a row in a table).  A collection contains data of the same datatype, but the data is unrelated (i.e., collect all last names, which are the same datatype, but obviously unrelated).

Peformance-wise, if you're selecting the majority of columns from a table, then selecting all of them via %ROWTYPE may not add any significant overhead, and in turn, makes the select into a record step much simpler.  If using the SET ROW for an update, and only one or a few columns are affected, all columns will be processed (you won't be able to tell that "King" was updated to "King").

In Closing

Using PL/SQL records can be fairly easy and they can add a lot more robustness to your code (especially when using anchored datatypes).  They can be used in anonymous and named blocks, and with some simple naming conventions, their identifiers will be clear (e.g., a variable you declare is prefixed with v underscore, and a record you declare has "rec" or record in its name, and the same follows with types). 

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.