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

Comments

  1. 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. 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. 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. 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. 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. 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. 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. 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. 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. Thanks, Iudith. I was not aware of this.

    ReplyDelete
  6. 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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…