Related Entries

Useful SQL*Plus headers
Dino eats frog
Oracle with Active Record
When is he free?
Gentle dive into analytic functions

« Oracle linkfest, November 2002
» Quality Software Management vol 3

Updating data in Oracle

What is there to write about this? Perhaps there is!

There are many different ways you can update data in Oracle. Or for that matter, in any RDBMS. The easiest thing to do is to write a simple update statement followed by a commit. It might not be the wisest thing to do always.

UPDATE statement

In its simple form, it involves:

UPDATE employees SET salary = salary + 100.0;

Though it is very easy, there is a situation where this may not work.

If the table has lots of records, and if you are updating many of them, there is a chance that your rollback segment might get exhausted.

If you encounter such situation, the solution is to process records in batches and then commit after every batch.

PL/SQL block

This indeed looks more cumbersome than the previous one. This can involve a cursor followed by update on each record:

commit_interval := 10000;
count := 0;
FOR rec_employee IS (SELECT * FROM employees FOR UPDATE OF salary)
    UPDATE employees SET salary = salary + 5 WHERE employee_id = rec_employee.employee_id;
    count := count + 1;
    IF count >= commit_interval THEN
        count := 0;
    END IF;

A periodic commit will ensure that the rollback segment is not filled up. Depending upon the size of your rollback segment, you might have to adjust the commit interval.

This too has a disadvantage. If your table is very very large - say 10 million records, this will take a lot of time if you want to update all the records. Such situations are very common in data uploads to data warehouses.

The next method, though it looks ridiculous can be very fast.

Export, process outside and then import back

It involves unloading the data into an plain text format, may be CSV. Then you use UNIX utilities like sed, awk or perl to update the file. After that, truncate the table and upload the data.

You can write a pretty quick SQL script that works as the inverse of SQL*Loader. Unload the data in some field separated format. Pipe (|) separated format is very good, if your data does not have this character.. CSV is good, but the field separator is not as straight forward as pipe, because you will have to put quotes around text that contains comma. Multi-character field separators can complicate things.

Once you’ve a flat file, depending upon the type of update you need, use common UNIX text processing tools to update the file. Believe me, this can be pretty much faster than Oracle updates.

After you get the updates done, truncate the table and then use SQL*Loader to upload the modified file using direct path.

I’ve very often used this method in data warehouses. Usual SQL and PL/SQL methods used to run for hours. Using UNIX tools used to get things done in less than an hour.

Though this is the most complex to implement this strategy, it is not without other troubles. If you need to do data validation with respect to other tables or constraints, this becomes impractical.


Even for a simple thing as an update, there is no one right way! Depending upon different requirements and your environment, you will have to strategize based upon how exactly you are going to solve a problem.

What will help? Get to know some tools. If you try to solve everything using Java (or PL/SQL or ASP) since that is your favorite, you might be creating technical solutions that don’t match exactly to practical problems. End of the day, what matters is if you could solve a problem, not which technology you used!

  1. Good one to understand the basics

    Posted by: Dragon_lee on January 9, 2003 07:19 PM
  2. When I try to emulate the cursor example, I keep getting a "fetch out of sequence" error.

    Any ideas?

    Posted by: Pepper on May 5, 2003 03:27 PM