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

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

The PL/SQL Bulk Load Operator

While bulk collect retrieves all the requested data from the database with two context switches, the bulk load will bulk manipulate the data in the same manner.  The term bulk load does not really describe this capability; a better name would be bulk change.  You can bulk update, delete, or insert.  Bulk loading uses the FORALL clause to tell the SQL engine to execute the command on all rows meeting the specification.  Although the FORALL clause looks like is FOR loop, it is different, primarily because there is no LOOP or END LOOP in the statement.

The example below selects over 12,000 object names and object ids from the database and then insert them into a table named big_one.  First create the table to hold the data.

SQL> create table big_one as
  2  select object_name, object_id from dba_objects
  3  where 2 = 1; 

Table created.

Now, bulk collect all the object names and ID numbers from the dba_objects  view and then bulk load then into the big_one table.  If you are not logged on as a user with DBA privilages the dba_objects view will not be available.  In this case use the user_objects view, which contains fewer objects.

SQL> set timing on
SQL> declare
  2    type name_tab is table of  
  3    type id_tab   is table of
  4    t_name name_tab;
  5    t_id   id_tab;
  6    cursor c1 is select object_name, object_id
  7                 from dba_objects;
  8  begin
  9    open c1;
 10    fetch c1 bulk collect into t_name, t_id;
 11    close c1;
 12    -- bulk insert
 13    forall indx in t_name.first..t_name.last
 14      insert into big_one values
 15        ( t_name(indx), t_id(indx));
 16  end;
 17  / 

Elapsed: 00:00:00.00
SQL> select count(*) from big_one; 

  COUNT (*)

Wow, this process read and inserted over 12,000 rows in so little time that it did not even register on the timer.  Notice that line 13 through 15 is actually one line of code.  The FORALL clause passes the nested table collections to the SQL engine, which loads the data into the tables and then returns to the PL/SQL engine.  These changes have not yet been committed and can still be rolled back if necessary. 

The FORALL can also delete or update rows.  Here we create a very large table by joining the dba_objects  view and the sales table using a Cartesian product .

SQL> create table bigger_one as
  2    select book_key, quantity, object_id
  3    from dba_objects cross join sales;
Table created.

SQL> select count(*) from bigger_one; 

  COUNT (*)

The bigger_one table has over a million rows.  We want to increase the quantity value by 10 for all rows with book_key equals B103, B112, and B114.  Finally we want to delete all the rows where the book_key equals B116, B106, and B109.

SQL> declare
  2    type book_tab is table of
  4    t_book_add book_tab :=
  5    t_book_del book_tab :=
  6  begin
  7    forall indx in
  8      update bigger_one
                      set quantity = quantity + 10
  9        where book_key = t_book_add(indx);
 10    forall indx in
 11      delete from bigger_one
 12        where book_key = t_book_del(indx);
 13  end;
 14  / 

Elapsed: 00:01:46.06

Even using bulk loads, the PL/SQL block took almost 2 minutes to complete.  Lines 4 and 5 defined the collection variables and instantiated the nested tables.  Lines 7 through 12 performed the actual work with two bulk loads.  Do not forget that the changes are not permanent until a commit is issued.

A number of the examples provided so far have used included functions.  Local functions are defined in the DECLARE section of the block.  These functions were required because they used some defined variable or type from the outer block.  There is a better way to share data and definitions within an application and that is by using PACKAGES.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

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


email BC:

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

Oracle® is the registered trademark of Oracle Corporation.