Skip to main content

Make the Most of PL/SQL Bulk Processing

The bulk processing features of PL/SQL (BULK COLLECT and FORALL) are key tools for improving performance of programs that currently rely on row-by-row processing, an example of which is shown below.

Use this blog post to quickly get to some of the best resources on bulk processing - from articles to quizzes to workouts to tutorials.

LiveSQL Tutorial

I offer a 19-module tutorial on all things bulk processing here. I complement the explanations with lots of code to run and explore, along with:
  • Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that module
  • Exercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up).

Oracle-BASE Content

You can always depend on Tim Hall to offer comprehensive coverage of SQL and PL/SQL features, with straightforward, easy-to-run code snippets to drive the points home. You'll find his coverage of bulk processing here.

Oracle Documentation

The Bulk SQL and Bulk Binding section of the PL/SQL Users Guide is packed full of syntax, links to related content, and tips on how to best take advantage of FORALL and BULK COLLECT.

Oracle Dev Gym Workouts

The Oracle Dev Gym offers multiple choices quizzes, workouts and classes on a wide variety of Oracle Database topics. Find below a set of four workouts (three featuring content by Tim Hall) on FORALL and BULK COLLECT.

BULK COLLECT by Tim Hall

Tim explores the BULK COLLECT feature of PL/SQL, which allows you to retrieve multiple rows with a single fetch. Note that Tim's article also covers FORALL, which is for multi-row, non-query DML (inserts, updates, deletes) and will be explored in a separate workout. After you read his article and check out the documentation, it's time to take four quizzes written by your truly to test your knowledge of this feature.

FORALL - Basic Concepts by Tim Hall

Tim offers a comprehensive review of bulk processing in PL/SQL; this workout focuses in on FORALL, covering the basic concepts behind this powerful performance enhancer. We complement Tim's article with a link to documentation and FORALL quizzes from the Dev Gym library.

FORALL and SAVE EXCEPTIONS by Tim Hall

Tim provides a comprehensive review of bulk processing in PL/SQL in this workout's leading exercise. Drill down to the SAVE EXCEPTIONS section of Tim's article to explore how to handle exceptions that may be raised when FORALL executes. Check out the documentation for more details. Then finish up with quizzes from your truly on SAVE EXCEPTIONS. Go beyond FORALL basics with this workout!

An Hour (more or less) of Bulk Processing Quizzes

Ten quizzes on FORALL and BULK COLLECT, ranging in difficulty from beginner to intermediate.

Other Blog Posts and Articles

My article in Oracle Magazine: Bulk Processing with BULK COLLECT and FORALL

Blog post: A checklist for Bulk Processing Conversions in PL/SQL
Labels:

Comments

  1. Pradosh PrustyJune 18, 2019 at 8:20 AM

    Hi Steven ,
    I have been following up your blog starting from the day i started my career in IT and all the blogs are great .

    I was trying few things on bulk collect and running into this error only for Collections defined using cursor%rowtype . I couldn't also find any explanations anywhere including oracle documentation and so is this post.

    EMP table definition :

    ID NUMBER
    NAME VARCHAR2(250)
    SALARY NUMBER

    EMP_COPY table definition :

    ID NUMBER
    NAME VARCHAR2(250)
    SALARY NUMBER

    Package Spec :

    create or replace PACKAGE test_api_1 AS

    -- Based on Table%ROWTYPE

    TYPE t_tab IS TABLE OF emp%ROWTYPE;
    --Parameterised Cursor on emp

    CURSOR emp_c(p_id number)
    IS
    SELECT id,name,salary from emp
    where id<=p_id;

    --Parameterised Cursor on emp_copy having less rows
    CURSOR emp_c1(p_id number)
    IS
    SELECT * from emp_copy
    where id<=p_id;

    --Collection Based on Cursror Type

    TYPE emps_type IS TABLE OF emp_c%ROWTYPE ;

    --Record Type having same column from EMP table

    TYPE rec_emps_type IS RECORD
    (ID NUMBER,
    NAME VARCHAR2(250),
    SALARY NUMBER);

    --Collection based on Record Type
    TYPE rec_emps_type_t is TABLE OF rec_emps_type;

    ---Works for RECORD TYPE and TABLE%ROWTYPE but not CURSOR%ROWTYPE..Asktom

    PROCEDURE test1;
    END TEST_API_1;
    /

    create or replace PACKAGE BODY test_api_1 AS

    PROCEDURE test1 IS
    l_tab1 t_tab;
    l_tab2 t_tab;


    emps_type_1 rec_emps_type_t;
    emps_type_2 rec_emps_type_t;


    emps_type_curr emps_type;
    emps_type_prev emps_type;


    BEGIN

    --- Collection Population and using TABLE with TABLE%ROWTYPE and Works fine

    SELECT *
    BULK COLLECT INTO l_tab1
    FROM emp where id<=200;

    SELECT *
    BULK COLLECT INTO l_tab2
    FROM emp where id<=190;


    DBMS_OUTPUT.put_line('Loop Through Collection');
    FOR cur_rec IN (SELECT *
    FROM TABLE(l_tab1)
    MINUS
    SELECT *
    FROM TABLE(l_tab2))
    LOOP
    DBMS_OUTPUT.put_line(cur_rec.id || ' : ' || cur_rec.name);
    null;
    END LOOP;

    --End of Collection Population and using TABLE with TABLE%ROWTYPE and Works fine

    --Collection Population and using RECORD Type



    SELECT *
    BULK COLLECT INTO emps_type_1
    FROM emp where id<=200;

    SELECT *
    BULK COLLECT INTO emps_type_2
    FROM emp where id<=190;

    FOR cur_rec IN (SELECT *
    FROM TABLE(emps_type_1)
    MINUS
    SELECT *
    FROM TABLE(emps_type_2))

    LOOP
    DBMS_OUTPUT.put_line(cur_rec.name);

    END LOOP;

    ---End of Collection Population and using RECORD Type works fine

    --Collection Population and using CURSOR%TYPE and doesn't work, Throws error if try , to access cur_rec.name

    OPEN emp_c(200);
    FETCH emp_c BULK COLLECT INTO emps_type_curr;
    CLOSE emp_c;

    OPEN emp_c(190);
    FETCH emp_c BULK COLLECT INTO emps_type_prev;
    CLOSE emp_c;




    FOR cur_rec IN (SELECT *
    FROM TABLE(emps_type_curr)
    MINUS
    SELECT *
    FROM TABLE(emps_type_prev))

    LOOP
    DBMS_OUTPUT.put_line(cur_rec.name);
    --Uncomment the above line , this compiles
    --DBMS_OUTPUT.put_line(cur_rec.id);
    dbms_output.put_line('Test1');
    END LOOP;


    --End Collection Population and using CURSOR%TYPE and doesn't work, Throws error if try , to access cur_rec.name


    END TEST1;

    END TEST_API_1;
    /

    Compilation Error Occurs :

    Error(87,36): PLS-00302: component 'NAME' must be declared

    if i comment the line DBMS_OUTPUT.put_line(cur_rec.name);
    code works fine .



    ReplyDelete
  2. StevenFeuersteinJune 18, 2019 at 8:34 AM

    Please take all of your code, load it and run it on livesql.oracle.com, mark the script as unlisted and send me the link - either post it here if you'd like or email it to Steven dot Feuerstein at oracle dot com.

    ReplyDelete
    Replies
    1. Pradosh PrustyJune 19, 2019 at 3:58 AM

      Hi Steven,
      My sincere apology for the incorrect links to the script .

      Below are the correct links to the scripts in the sequence they need to be executed


      https://livesql.oracle.com/apex/livesql/s/ijynhu4mygq5npvux7881vbwg

      https://livesql.oracle.com/apex/livesql/s/ijyi0vzy8n3fjbcdz7yk668bq

      https://livesql.oracle.com/apex/livesql/s/ijyneck9tfdh9b8hgvk6bmtlm

      Delete
    2. StevenFeuersteinJune 19, 2019 at 5:46 AM

      Thanks for doing that. I combined them into a single script, ran it, and didn't see any errors. Here's my link:

      https://livesql.oracle.com/apex/livesql/s/ijywp66pg5hsl5sh7t8oy0yie

      So: what is your question? :-)

      Delete
    3. StevenFeuersteinJune 19, 2019 at 8:30 AM

      I'd like to switch off of communicating via blog post comments. Please send me an email so I can reply via email. In particular, I saw this comment:

      ---Works for RECORD TYPE and TABLE%ROWTYPE but not CURSOR%ROWTYPE..Asktom

      Does this mean you've already found an answer on AskTOM that identifies the problem? If so please provide the URL.

      Delete
  3. Pradosh PrustyJune 19, 2019 at 7:11 AM

    Hi Steve ,
    Thank you for your quick replies .

    If you can uncomment line number 87 in the Package body code i.e. DBMS_OUTPUT.put_line(cur_rec.name); and try to compile the package , i get compilation error Error(87,36): PLS-00302: component 'NAME' must be declared .

    My Question is : Why can't i access the cur_rec.name ?

    Thanks
    Pradosh

    ReplyDelete
    Replies
    1. StevenFeuersteinJune 19, 2019 at 11:09 AM

      It looks like a bug (or a documented limitation - but I haven't found the doc for that yet! :-)). Here's a simple script everyone can use to reproduce it:

      DROP TABLE t
      /

      DROP PACKAGE pkg
      /

      CREATE TABLE t
      (
      n NUMBER,
      v VARCHAR2 (10)
      )
      /

      BEGIN
      INSERT INTO t
      VALUES (1, 'a');

      INSERT INTO t
      VALUES (2, 'b');

      COMMIT;
      END;
      /

      CREATE OR REPLACE PACKAGE pkg
      AUTHID DEFINER
      IS
      TYPE tt IS TABLE OF t%ROWTYPE;

      CURSOR c
      IS
      SELECT * FROM t;

      TYPE ct IS TABLE OF c%ROWTYPE;

      type r is record (n number, v varchar2(10));

      type rt is table of r;
      END;
      /

      DECLARE
      l_t pkg.tt;
      BEGIN
      SELECT *
      BULK COLLECT INTO l_t
      FROM t;

      FOR rec IN (SELECT * FROM TABLE (l_t))
      LOOP
      DBMS_OUTPUT.put_line (rec.v);
      END LOOP;
      END;
      /

      DECLARE
      l_t pkg.ct;
      BEGIN
      SELECT *
      BULK COLLECT INTO l_t
      FROM t;

      FOR rec IN (SELECT * FROM TABLE (l_t))
      LOOP
      DBMS_OUTPUT.put_line (rec.v);
      END LOOP;
      END;
      /

      DECLARE
      l_t pkg.rt;
      BEGIN
      SELECT *
      BULK COLLECT INTO l_t
      FROM t;

      FOR rec IN (SELECT * FROM TABLE (l_t))
      LOOP
      DBMS_OUTPUT.put_line (rec.v);
      END LOOP;
      END;
      /

      I've asked Pradosh to submit a bug report, and I will follow up.

      The workaround is to create a record type that matches the cursor select list (select * is used for simplicity - if you really did have a select *, you could just use table%rowtype and avoid this issues) and then declare a collection type as shown in the third block. That works fine.

      Delete
    2. Pradosh PrustyJune 20, 2019 at 12:18 AM

      Hi Steven ,
      Thanks for looking it into it and appreciate your quick replies .
      As suggested , i have raised a SR with Oracle .

      The workaround is there , but the issue is - i have to create a custom RECORD type always , has it been CURSOR%ROWTYPE , i would have avoided creating RECORD TYPE .

      Thanks
      Pradosh

      Delete
  4. iudithJuly 15, 2019 at 11:04 AM

    Hello Steven, All,

    Just a short addition to your nice setup case above:

    The following block works:

    DECLARE
    l_t pkg.ct;
    BEGIN
    SELECT *
    BULK COLLECT INTO l_t
    FROM t;

    FOR rec IN (SELECT * FROM TABLE (l_t))
    LOOP
    -- DBMS_OUTPUT.put_line (rec.v);
    DBMS_OUTPUT.put_line ( rec.attr_1 || ' - ' || rec.attr_2 );
    END LOOP;
    END;
    /

    1 - a
    2 - b


    I just found this during some other research ... and it is really surprising !

    That is, when using a cursor-based record type and "passing" a collection of that type
    as argument to TABLE() , the resulting column names are always ATTR_1, ATTR_2,
    ... ATTR_n, instead of the original cursor field names.

    In "pure" PL/SQL usage, instead, the original cursor field names are preserved !

    What I am really curious at this point is whether this is a new behavior (or bug !)
    or it also existed in older versions.

    Using such a collection type variable as argument to TABLE() was only added
    in 12c, but using for example a pipelined function that returns such a package-level
    collection type was already available since 10g or maybe even 9i .

    I can hardly believe that no one indeed has encountered such a scenario for such a long time, without reporting it.

    Thanks a lot & Best Regards,
    Iudith


    ReplyDelete
  5. StevenFeuersteinJuly 17, 2019 at 3:53 AM

    Thanks, Iudith. I was not aware of this.

    ReplyDelete
  6. Pradosh PrustyJuly 17, 2019 at 7:35 AM

    Hi Iudith ,
    Thank you so much for your research and posting your findings . I did raise a SR with Oracle , but it got closed .


    If i can only access using ATTR_1 ,ATTR_2 i guess it is a Bug, but only Oracle can confirm it.

    Thanks
    Pradosh

    ReplyDelete

Post a Comment

Popular posts from this blog

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,
4 comments
Read more

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p
24 comments
Read more