Bulk Operations in PL/SQL


When I was in Chicago for the OPP2008 and APEXposed event I talked to someone who seems to have trouble understanding bulk operations in PL/SQL. I helped him out by providing a test/demo script, that shows how it could be done. First of all, one of the most important rules of thumb that I got from the seminar (almost everyone talked about this):

  • If you can do it in SQL, do it in SQL
  • If you can’t, use PL/SQL
  • If you still can’t, then resort to some other tool, like Java, VB or whatever…

If you can do your update, entirely in SQL, then that is the fastest solution. But in the case described to me, there is a lot going on between the select (from table) and the update (maybe even some other table).

To work with the demonstration files, we need to create a table that we can use for this demonstration:

<span class="lnum">   1:  </span><span class="kwrd">DELETE</span> EMP;
<span class="lnum">   2:  </span><span class="kwrd">DELETE</span> DEPT;
<span class="lnum">   3:  </span><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> EMP;
<span class="lnum">   4:  </span><span class="kwrd">DROP</span> <span class="kwrd">TABLE</span> DEPT;
<span class="lnum">   5:  </span>&nbsp;
<span class="lnum">   6:  </span><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> DEPT
<span class="lnum">   7:  </span>       ( DEPTNO NUMBER(2)
<span class="lnum">   8:  </span>       , DNAME  VARCHAR2(14)
<span class="lnum">   9:  </span>       , LOC    VARCHAR2(13) 
<span class="lnum">  10:  </span>       );
<span class="lnum">  11:  </span>&nbsp;
<span class="lnum">  12:  </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (10, <span class="str">'ACCOUNTING'</span>, <span class="str">'NEW YORK'</span>);
<span class="lnum">  13:  </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (20, <span class="str">'RESEARCH'</span>,   <span class="str">'DALLAS'</span>);
<span class="lnum">  14:  </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (30, <span class="str">'SALES'</span>,      <span class="str">'CHICAGO'</span>);
<span class="lnum">  15:  </span>INSERT <span class="kwrd">INTO</span> DEPT <span class="kwrd">VALUES</span> (40, <span class="str">'OPERATIONS'</span>, <span class="str">'BOSTON'</span>);
<span class="lnum">  16:  </span>&nbsp;
<span class="lnum">  17:  </span><span class="kwrd">CREATE</span> <span class="kwrd">TABLE</span> EMP
<span class="lnum">  18:  </span>       ( EMPNO    NUMBER(4) <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>
<span class="lnum">  19:  </span>       , ENAME    VARCHAR2(10)
<span class="lnum">  20:  </span>       , JOB      VARCHAR2(9)
<span class="lnum">  21:  </span>       , MGR      NUMBER(4)
<span class="lnum">  22:  </span>       , HIREDATE <span class="kwrd">DATE</span>
<span class="lnum">  23:  </span>       , SAL      NUMBER(7, 2)
<span class="lnum">  24:  </span>       , COMM     NUMBER(7, 2)
<span class="lnum">  25:  </span>       , DEPTNO   NUMBER(2)
<span class="lnum">  26:  </span>       );
<span class="lnum">  27:  </span>&nbsp;
<span class="lnum">  28:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7369, <span class="str">'SMITH'</span>,  <span class="str">'CLERK'</span>,     7902, TO_DATE(<span class="str">'17-DEC-1980'</span>, <span class="str">'DD-MON-YYYY'</span>),  800, <span class="kwrd">NULL</span>, 20);
<span class="lnum">  29:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7499, <span class="str">'ALLEN'</span>,  <span class="str">'SALESMAN'</span>,  7698, TO_DATE(<span class="str">'20-FEB-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 1600,  300, 30);
<span class="lnum">  30:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7521, <span class="str">'WARD'</span>,   <span class="str">'SALESMAN'</span>,  7698, TO_DATE(<span class="str">'22-FEB-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 1250,  500, 30);
<span class="lnum">  31:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7566, <span class="str">'JONES'</span>,  <span class="str">'MANAGER'</span>,   7839, TO_DATE(<span class="str">'2-APR-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 2975, <span class="kwrd">NULL</span>, 20);
<span class="lnum">  32:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7654, <span class="str">'MARTIN'</span>, <span class="str">'SALESMAN'</span>,  7698, TO_DATE(<span class="str">'28-SEP-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 1250, 1400, 30);
<span class="lnum">  33:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7698, <span class="str">'BLAKE'</span>,  <span class="str">'MANAGER'</span>,   7839, TO_DATE(<span class="str">'1-MAY-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 2850, <span class="kwrd">NULL</span>, 30);
<span class="lnum">  34:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7782, <span class="str">'CLARK'</span>,  <span class="str">'MANAGER'</span>,   7839, TO_DATE(<span class="str">'9-JUN-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 2450, <span class="kwrd">NULL</span>, 10);
<span class="lnum">  35:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7788, <span class="str">'SCOTT'</span>,  <span class="str">'ANALYST'</span>,   7566, TO_DATE(<span class="str">'09-DEC-1982'</span>, <span class="str">'DD-MON-YYYY'</span>), 3000, <span class="kwrd">NULL</span>, 20);
<span class="lnum">  36:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7839, <span class="str">'KING'</span>,   <span class="str">'PRESIDENT'</span>, <span class="kwrd">NULL</span>, TO_DATE(<span class="str">'17-NOV-1981'</span>, <span class="str">'DD-MON-YYYY'</span>), 5000, <span class="kwrd">NULL</span>, 10);
<span class="lnum">  37:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7844, <span class="str">'TURNER'</span>, <span class="str">'SALESMAN'</span>,  7698, TO_DATE(<span class="str">'8-SEP-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 1500,    0, 30);
<span class="lnum">  38:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7876, <span class="str">'ADAMS'</span>,  <span class="str">'CLERK'</span>,     7788, TO_DATE(<span class="str">'12-JAN-1983'</span>, <span class="str">'DD-MON-YYYY'</span>), 1100, <span class="kwrd">NULL</span>, 20);
<span class="lnum">  39:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7900, <span class="str">'JAMES'</span>,  <span class="str">'CLERK'</span>,     7698, TO_DATE(<span class="str">'3-DEC-1981'</span> , <span class="str">'DD-MON-YYYY'</span>),  950, <span class="kwrd">NULL</span>, 30);
<span class="lnum">  40:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7902, <span class="str">'FORD'</span>,   <span class="str">'ANALYST'</span>,   7566, TO_DATE(<span class="str">'3-DEC-1981'</span> , <span class="str">'DD-MON-YYYY'</span>), 3000, <span class="kwrd">NULL</span>, 20);
<span class="lnum">  41:  </span>INSERT <span class="kwrd">INTO</span> EMP <span class="kwrd">VALUES</span> (7934, <span class="str">'MILLER'</span>, <span class="str">'CLERK'</span>,     7782, TO_DATE(<span class="str">'23-JAN-1982'</span>, <span class="str">'DD-MON-YYYY'</span>), 1300, <span class="kwrd">NULL</span>, 10);
<span class="lnum">  42:  </span>&nbsp;
<span class="lnum">  43:  </span><span class="kwrd">COMMIT</span>;

Then we create the code as a stored procedure

<span class="lnum">   1:  </span><span class="kwrd">CREATE</span> <span class="kwrd">OR</span> REPLACE <span class="kwrd">PROCEDURE</span> bulk_demonstration
<span class="lnum">   2:  </span><span class="kwrd">IS</span>
<span class="lnum">   3:  </span>  <span class="rem">-- The maximum rows collected in the bulk collect operation</span>
<span class="lnum">   4:  </span>  c_maxrows                       CONSTANT PLS_INTEGER := 5;
<span class="lnum">   5:  </span>&nbsp;
<span class="lnum">   6:  </span>  <span class="rem">-- Subtypes based on the columns in the cursor</span>
<span class="lnum">   7:  </span>  SUBTYPE empno_t <span class="kwrd">IS</span> emp.empno%TYPE;
<span class="lnum">   8:  </span>  SUBTYPE ename_t <span class="kwrd">IS</span> emp.ename%TYPE;
<span class="lnum">   9:  </span>  SUBTYPE job_t <span class="kwrd">IS</span> emp.job%TYPE;
<span class="lnum">  10:  </span>  SUBTYPE mgr_t <span class="kwrd">IS</span> emp.mgr%TYPE;
<span class="lnum">  11:  </span>  SUBTYPE hiredate_t <span class="kwrd">IS</span> emp.hiredate%TYPE;
<span class="lnum">  12:  </span>  SUBTYPE sal_t <span class="kwrd">IS</span> emp.sal%TYPE;
<span class="lnum">  13:  </span>  SUBTYPE comm_t <span class="kwrd">IS</span> emp.comm%TYPE;
<span class="lnum">  14:  </span>  SUBTYPE deptno_t <span class="kwrd">IS</span> emp.deptno%TYPE;
<span class="lnum">  15:  </span>&nbsp;
<span class="lnum">  16:  </span>  <span class="rem">-- Column Collections for every column in the cursor</span>
<span class="lnum">  17:  </span>  TYPE empno_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> empno_t       <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  18:  </span>  TYPE ename_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> ename_t       <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  19:  </span>  TYPE job_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> job_t           <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  20:  </span>  TYPE mgr_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> mgr_t           <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  21:  </span>  TYPE hiredate_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> hiredate_t <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  22:  </span>  TYPE sal_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> sal_t           <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  23:  </span>  TYPE comm_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> comm_t         <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  24:  </span>  TYPE deptno_cc <span class="kwrd">IS</span> <span class="kwrd">TABLE</span> <span class="kwrd">OF</span> deptno_t     <span class="kwrd">INDEX</span> <span class="kwrd">BY</span> BINARY_INTEGER;
<span class="lnum">  25:  </span>&nbsp;
<span class="lnum">  26:  </span>  <span class="rem">-- The cursor used</span>
<span class="lnum">  27:  </span>  <span class="kwrd">CURSOR</span> emp_cur
<span class="lnum">  28:  </span>  <span class="kwrd">IS</span>
<span class="lnum">  29:  </span>    <span class="kwrd">SELECT</span> emp.empno
<span class="lnum">  30:  </span>         , emp.ename
<span class="lnum">  31:  </span>         , emp.job
<span class="lnum">  32:  </span>         , emp.mgr
<span class="lnum">  33:  </span>         , emp.hiredate
<span class="lnum">  34:  </span>         , emp.sal
<span class="lnum">  35:  </span>         , emp.comm
<span class="lnum">  36:  </span>         , emp.deptno
<span class="lnum">  37:  </span>      <span class="kwrd">FROM</span> emp;
<span class="lnum">  38:  </span>&nbsp;
<span class="lnum">  39:  </span>  <span class="rem">-- Local variables to 'catch' the results from the cursor</span>
<span class="lnum">  40:  </span>  l_empno                                  empno_cc;
<span class="lnum">  41:  </span>  l_ename                                  ename_cc;
<span class="lnum">  42:  </span>  l_job                                    job_cc;
<span class="lnum">  43:  </span>  l_mgr                                    mgr_cc;
<span class="lnum">  44:  </span>  l_hiredate                               hiredate_cc;
<span class="lnum">  45:  </span>  l_sal                                    sal_cc;
<span class="lnum">  46:  </span>  l_comm                                   comm_cc;
<span class="lnum">  47:  </span>  l_deptno                                 deptno_cc;
<span class="lnum">  48:  </span>  <span class="rem">-- local function to do something with the data</span>
<span class="lnum">  49:  </span>  <span class="kwrd">function</span> raisesal(sal_in sal_t) <span class="kwrd">return</span> sal_t
<span class="lnum">  50:  </span>  <span class="kwrd">is</span>
<span class="lnum">  51:  </span>    l_returnvalue sal_t;
<span class="lnum">  52:  </span>  <span class="kwrd">begin</span>
<span class="lnum">  53:  </span>    l_returnvalue := sal_in * 1.1;
<span class="lnum">  54:  </span>    <span class="kwrd">RETURN</span> l_returnvalue;
<span class="lnum">  55:  </span>  <span class="kwrd">end</span> raisesal;
<span class="lnum">  56:  </span>    
<span class="lnum">  57:  </span><span class="kwrd">BEGIN</span>
<span class="lnum">  58:  </span>  <span class="rem">-- open the cursor</span>
<span class="lnum">  59:  </span>  <span class="kwrd">OPEN</span> emp_cur;
<span class="lnum">  60:  </span>  <span class="rem">-- start a simple loop</span>
<span class="lnum">  61:  </span>  LOOP
<span class="lnum">  62:  </span>    <span class="rem">-- clear out the collections</span>
<span class="lnum">  63:  </span>    l_empno.<span class="kwrd">delete</span>;
<span class="lnum">  64:  </span>    l_ename.<span class="kwrd">delete</span>;
<span class="lnum">  65:  </span>    l_job.<span class="kwrd">delete</span>;
<span class="lnum">  66:  </span>    l_mgr.<span class="kwrd">delete</span>;
<span class="lnum">  67:  </span>    l_hiredate.<span class="kwrd">delete</span>;
<span class="lnum">  68:  </span>    l_sal.<span class="kwrd">delete</span>;
<span class="lnum">  69:  </span>    l_comm.<span class="kwrd">delete</span>;
<span class="lnum">  70:  </span>    l_deptno.<span class="kwrd">delete</span>;
<span class="lnum">  71:  </span>    <span class="rem">-- fetch from the cursor using bulk collect for the result, but limited to limit</span>
<span class="lnum">  72:  </span>    <span class="kwrd">FETCH</span> emp_cur <span class="kwrd">BULK</span> COLLECT <span class="kwrd">INTO</span> l_empno
<span class="lnum">  73:  </span>                                  , l_ename
<span class="lnum">  74:  </span>                                  , l_job
<span class="lnum">  75:  </span>                                  , l_mgr
<span class="lnum">  76:  </span>                                  , l_hiredate
<span class="lnum">  77:  </span>                                  , l_sal
<span class="lnum">  78:  </span>                                  , l_comm
<span class="lnum">  79:  </span>                                  , l_deptno
<span class="lnum">  80:  </span>    <span class="kwrd">LIMIT</span> c_maxrows;
<span class="lnum">  81:  </span>    <span class="rem">-- if we fetched any data</span>
<span class="lnum">  82:  </span>    <span class="kwrd">IF</span> l_empno.<span class="kwrd">count</span> &gt; 0 <span class="kwrd">THEN</span>
<span class="lnum">  83:  </span>      <span class="rem">-- do some complicated stuff</span>
<span class="lnum">  84:  </span>      <span class="kwrd">FOR</span> idx <span class="kwrd">IN</span> l_empno.<span class="kwrd">first</span>..l_empno.<span class="kwrd">last</span> LOOP
<span class="lnum">  85:  </span><span class="rem">--        l_sal(idx) := l_sal(idx) * 1.1;</span>
<span class="lnum">  86:  </span>        l_sal(idx) := raisesal(l_sal(idx));
<span class="lnum">  87:  </span>      <span class="kwrd">END</span> LOOP;
<span class="lnum">  88:  </span>      <span class="rem">-- update the records in the database, using a bulk operation</span>
<span class="lnum">  89:  </span>      FORALL idx <span class="kwrd">IN</span> l_empno.<span class="kwrd">first</span>..l_empno.<span class="kwrd">last</span>
<span class="lnum">  90:  </span>        <span class="kwrd">UPDATE</span> emp
<span class="lnum">  91:  </span>           <span class="kwrd">SET</span> sal = l_sal(idx)
<span class="lnum">  92:  </span>         <span class="kwrd">WHERE</span> empno = l_empno(idx);
<span class="lnum">  93:  </span>    <span class="kwrd">END</span> <span class="kwrd">IF</span>;
<span class="lnum">  94:  </span>    <span class="rem">-- exit when we didn't fetch our maximum rows</span>
<span class="lnum">  95:  </span>    <span class="kwrd">EXIT</span> <span class="kwrd">WHEN</span> l_empno.<span class="kwrd">count</span> &lt; c_maxrows;
<span class="lnum">  96:  </span>  <span class="kwrd">END</span> LOOP;
<span class="lnum">  97:  </span><span class="kwrd">END</span> bulk_demonstration;

Then a small script to see if it works

<span class="lnum">   1:  </span>CLEAR screen
<span class="lnum">   2:  </span><span class="kwrd">SET</span> serveroutput <span class="kwrd">on</span>
<span class="lnum">   3:  </span><span class="kwrd">SELECT</span> emp.empno
<span class="lnum">   4:  </span>     , emp.sal
<span class="lnum">   5:  </span>  <span class="kwrd">FROM</span> emp;
<span class="lnum">   6:  </span><span class="kwrd">EXEC</span> bulk_demonstration;
<span class="lnum">   7:  </span><span class="kwrd">SELECT</span> emp.empno
<span class="lnum">   8:  </span>     , emp.sal
<span class="lnum">   9:  </span>  <span class="kwrd">FROM</span> emp;

Let’s take the code from the stored procedure step by step:

Line 5: Define a constant to hold the maximum rows collected in a bulk collect operation

4Define a constant to hold the maximum rows collected in a bulk collect operation
This should of course be bigger than 5, but for demonstration purposes this is a nice value
7-14Subtypes defined on the columns in the table
This is done so these types can be used in collections, parameters and variables
17-24Collection types based on the column types
27-37The cursor used to retrieve the data from the table
40-47Variables based on the collection types, to catch the results from the cursor
In Oracle 11G you can catch the results into records instead of lots of scalar collections
49-55A simple inline function to demonstrate the usage of the data
59Open the cursor
Like you would always open it
61Start of a simple loop
63-70Clear out the collections
To make sure they only hold the retrieved data and no old data
72-80Fetch the records from the cursor with a max of whatever the constant is set to
82Check if any data was retrieved
84-87A for loop to process the data just retrieved
89-92Write all data back to the table in one pass
95Exit the loop if we didn’t retrieve the maximum number of rows


The really interesting parts of this code are of course lines 72-80 where we retrieve the data from the cursor in a single pass. The BULK COLLECT keyword tells the engines to do a single switch and retrieve all data in one pass. The LIMIT keyword is included to preserve memory. Since all data will be retrieved this could lead to memory problems. Especially in a multi-user environment.

Then there is the FORALL statement (lines 89-92) that writes all the data in the collections back to the table in one pass.

Normally we would process the contents of the cursor record by record, but by adding these keywords we use the bulk operations provided by Oracle to speed up things.

Hope this helps your understanding of the Bulk Operations in PL/SQL.

This entry is crossposted at http://blog.bar-solutions.com.


About Author

Patrick Barel is a PL/SQL Developer for AMIS Services in the Netherlands. Besides working with SQL and PL/SQL did he co-develop CodeGen and wrote different plug-ins for PL/SQL Developer. He publishes articles on the AMIS Technology blog and on his own blog http://blog.bar-solutions.com/.

1 Comment

  1. I don’t think it is necessary to create a bunch of scalar collections, even in Oracle 10g…
    SQL> select *
    2 from v$version
    3 /
    Oracle Database 10g Enterprise Edition Release – 64bi
    PL/SQL Release – Production
    CORE Production
    TNS for IBM/AIX RISC System/6000: Version – Productio
    NLSRTL Version – Production
    SQL> set serveroutput on
    SQL> create table emp
    2 (empno number
    3 ,ename varchar2(30)
    4 );
    Table created.
    SQL> insert into emp
    2 select rownum
    3 , object_name
    4 from all_objects
    5 where rownum
    SQL> declare
    2 cursor c_emp
    3 is
    4 select empno
    5 , ename
    6 from emp
    7 ;
    8 type emp_tt is table of emp%rowtype
    9 index by binary_integer;
    10 emp_t emp_tt;
    11 begin
    12 open c_emp;
    13 loop
    14 fetch c_emp bulk collect into emp_t;
    15 exit when c_emp%notfound;
    16 end loop;
    17 close c_emp;
    18 for i in 1 .. emp_t.count
    19 loop
    20 dbms_output.put_line (emp_t(i).ename||emp_t(i).empno);
    21 end loop;
    22 end;
    23 /
    PL/SQL procedure successfully completed.

    This comment is cross-posted on Bar-Solutions… ;-)